/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
using System.Xml;
using System.Text;
using System.Collections;
using System.Threading;

namespace SplendidCRM.Reports
{
	/// <summary>
	///		Summary description for QueryBuilder.
	/// </summary>
	public class QueryBuilder : SplendidControl
	{
		protected Label           lblError                ;
		protected RdlDocument     rdl                = null;
		protected DropDownList    lstMODULE               ;
		protected DropDownList    lstRELATED              ;
		protected DropDownList    lstMODULE_COLUMN_SOURCE ;
		protected CheckBox        chkSHOW_QUERY           ;

		protected string          sReportSQL              ;
		protected DataGrid        dgFilters               ;
		protected HtmlInputHidden txtFILTER_ID            ;
		protected DropDownList    lstFILTER_COLUMN_SOURCE ;
		protected DropDownList    lstFILTER_COLUMN        ;
		protected DropDownList    lstFILTER_OPERATOR      ;
		protected Label           lblMODULE               ;
		protected Label           lblRELATED              ;
		protected Label           lblMODULE_COLUMN_SOURCE ;
		protected Label           lblFILTER_COLUMN_SOURCE ;
		protected Label           lblFILTER_COLUMN        ;
		protected Label           lblFILTER_OPERATOR_TYPE ;
		protected Label           lblFILTER_OPERATOR      ;
		protected Label           lblFILTER_ID            ;
		
		protected HtmlInputHidden txtFILTER_SEARCH_ID        ;
		protected HtmlInputHidden txtFILTER_SEARCH_DATA_TYPE ;
		protected TextBox         txtFILTER_SEARCH_TEXT      ;
		protected TextBox         txtFILTER_SEARCH_TEXT2     ;
		protected DropDownList    lstFILTER_SEARCH_DROPDOWN  ;
		protected ListBox         lstFILTER_SEARCH_LISTBOX   ;
		protected Button          btnFILTER_SEARCH_SELECT    ;
		protected Label           lblFILTER_AND_SEPARATOR    ;

		protected _controls.DatePicker ctlFILTER_SEARCH_START_DATE;
		protected _controls.DatePicker ctlFILTER_SEARCH_END_DATE  ;

		protected Literal         litREPORT_QUERY         ;
		protected Literal         litREPORT_RDL           ;
		protected string[]        arrModules              ;
		protected bool            bUserSpecific           = false;
		protected bool            bPrimaryKeyOnly         = true;
		protected TableRow        trRelated               ;
		protected TableRow        trColumnSource          ;
		protected TableRow        trModule                ;

		// 10/23/2010 Paul.  Provide a way to tap into the events. This is needed by the RulesWizard. 
		public CommandEventHandler Command ;

		public string Modules
		{
			get { return (arrModules == null ? String.Empty : String.Join(",", arrModules)); }
			set { arrModules = value.Replace(" ", "").Split(','); }
		}

		public bool UserSpecific
		{
			get { return bUserSpecific; }
			set { bUserSpecific = value; }
		}

		public bool PrimaryKeyOnly
		{
			get { return bPrimaryKeyOnly; }
			set { bPrimaryKeyOnly = value; }
		}

		public bool ShowRelated
		{
			get { return trRelated.Visible; }
			set { trRelated.Visible = value; }
		}

		public bool ShowColumnSource
		{
			get { return trColumnSource.Visible; }
			set { trColumnSource.Visible = value; }
		}

		public bool ShowModule
		{
			get { return trModule.Visible; }
			set { trModule.Visible = value; }
		}

		public string ReportSQL
		{
			get { return sReportSQL; }
		}

		public string ReportRDL
		{
			get { return rdl.OuterXml; }
		}

		protected void ResetSearchText()
		{
			lstFILTER_COLUMN_SOURCE.SelectedIndex = 0;
			lstFILTER_COLUMN_SOURCE_Changed(null, null);
			lstFILTER_COLUMN.SelectedIndex = 0;
			lstFILTER_COLUMN_Changed(null, null);
			lstFILTER_OPERATOR.SelectedIndex = 0;
			lstFILTER_OPERATOR_Changed(null, null);

			txtFILTER_ID               .Value    = String.Empty;
			lblFILTER_ID               .Text     = String.Empty;
			txtFILTER_SEARCH_TEXT      .Text     = String.Empty;
			txtFILTER_SEARCH_TEXT2     .Text     = String.Empty;
			ctlFILTER_SEARCH_START_DATE.DateText = String.Empty;
			ctlFILTER_SEARCH_END_DATE  .DateText = String.Empty;
		}

		#region Page_Command
		protected void Page_Command(Object sender, CommandEventArgs e)
		{
			try
			{
				if ( e.CommandName == "Filters.Cancel" )
				{
					ResetSearchText();
					if ( Command != null )
						Command(sender, e);
				}
				else if ( e.CommandName == "Filters.Add" )
				{
					ResetSearchText();
					if ( Command != null )
						Command(sender, e);
				}
				else if ( e.CommandName == "Filters.Delete" )
				{
					FiltersDelete(Sql.ToString(e.CommandArgument));
					ResetSearchText();
					if ( Command != null )
						Command(sender, e);
				}
				else if ( e.CommandName == "Filters.Edit" )
				{
					string sFILTER_ID = Sql.ToString(e.CommandArgument);
					string sMODULE_NAME  = String.Empty;
					string sDATA_FIELD   = String.Empty;
					string sDATA_TYPE    = String.Empty;
					string sOPERATOR     = String.Empty;
					string sSEARCH_TEXT1 = String.Empty;
					string sSEARCH_TEXT2 = String.Empty;
					string[] arrSEARCH_TEXT = new string[0];
					FiltersGet(sFILTER_ID, ref sMODULE_NAME, ref sDATA_FIELD, ref sDATA_TYPE, ref sOPERATOR, ref arrSEARCH_TEXT );
					txtFILTER_ID               .Value    = sFILTER_ID;
					lblFILTER_ID               .Text     = txtFILTER_ID.Value;
					txtFILTER_SEARCH_DATA_TYPE .Value    = sDATA_TYPE;
					txtFILTER_SEARCH_TEXT      .Text     = String.Empty;
					txtFILTER_SEARCH_TEXT2     .Text     = String.Empty;
					ctlFILTER_SEARCH_START_DATE.DateText = String.Empty;
					ctlFILTER_SEARCH_END_DATE  .DateText = String.Empty;
					
					// 08/19/2010 Paul.  Check the list before assigning the value. 
					Utils.SetSelectedValue(lstFILTER_COLUMN_SOURCE, sMODULE_NAME);
					lstFILTER_COLUMN_SOURCE_Changed(null, null);
					
					// 08/19/2010 Paul.  Check the list before assigning the value. 
					Utils.SetSelectedValue(lstFILTER_COLUMN       , sDATA_FIELD );
					lstFILTER_COLUMN_Changed(null, null);
					// 08/19/2010 Paul.  Check the list before assigning the value. 
					Utils.SetSelectedValue(lstFILTER_OPERATOR     , sOPERATOR   );
					lstFILTER_OPERATOR_Changed(null, null);
					
					if ( arrSEARCH_TEXT.Length > 0 )
						sSEARCH_TEXT1 = arrSEARCH_TEXT[0];
					if ( arrSEARCH_TEXT.Length > 1 )
						sSEARCH_TEXT2 = arrSEARCH_TEXT[1];
					
					// 07/06/2007 Paul.  ansistring is treated the same as string. 
					string sCOMMON_DATA_TYPE = sDATA_TYPE;
					if ( sCOMMON_DATA_TYPE == "ansistring" )
						sCOMMON_DATA_TYPE = "string";
					switch ( sCOMMON_DATA_TYPE )
					{
						case "string":
						{
							switch ( sOPERATOR )
							{
								case "equals"        :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "contains"      :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "starts_with"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "ends_with"     :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "not_equals_str":  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "empty"         :  break;
								case "not_empty"     :  break;
							}
							break;
						}
						case "datetime":
						{
							if ( arrSEARCH_TEXT.Length > 0 )
							{
								DateTime dtSEARCH_TEXT1 = DateTime.MinValue;
								DateTime dtSEARCH_TEXT2 = DateTime.MinValue;
								if ( !(sOPERATOR.EndsWith("_after") || sOPERATOR.EndsWith("_before") || sOPERATOR.EndsWith("_old")) )
								{
									dtSEARCH_TEXT1 = DateTime.ParseExact(sSEARCH_TEXT1, "yyyy/MM/dd", Thread.CurrentThread.CurrentCulture.DateTimeFormat);
									dtSEARCH_TEXT2 = DateTime.MinValue;
									if ( arrSEARCH_TEXT.Length > 1 )
										dtSEARCH_TEXT2 = DateTime.ParseExact(sSEARCH_TEXT2, "yyyy/MM/dd", Thread.CurrentThread.CurrentCulture.DateTimeFormat);
								}
								switch ( sOPERATOR )
								{
									case "on"               :  ctlFILTER_SEARCH_START_DATE.DateText = dtSEARCH_TEXT1.ToShortDateString();  break;
									case "before"           :  ctlFILTER_SEARCH_START_DATE.DateText = dtSEARCH_TEXT1.ToShortDateString();  break;
									case "after"            :  ctlFILTER_SEARCH_START_DATE.DateText = dtSEARCH_TEXT1.ToShortDateString();  break;
									case "not_equals_str"   :  ctlFILTER_SEARCH_START_DATE.DateText = dtSEARCH_TEXT1.ToShortDateString();  break;
									case "between_dates"    :
										ctlFILTER_SEARCH_START_DATE.DateText = dtSEARCH_TEXT1.ToShortDateString();
										if ( arrSEARCH_TEXT.Length > 1 )
											ctlFILTER_SEARCH_END_DATE  .DateText = dtSEARCH_TEXT2.ToShortDateString();
										break;
									case "empty"            :  break;
									case "not_empty"        :  break;
									case "is_before"        :  break;
									case "is_after"         :  break;
									case "tp_yesterday"     :  break;
									case "tp_today"         :  break;
									case "tp_tomorrow"      :  break;
									case "tp_last_7_days"   :  break;
									case "tp_next_7_days"   :  break;
									case "tp_last_month"    :  break;
									case "tp_this_month"    :  break;
									case "tp_next_month"    :  break;
									case "tp_last_30_days"  :  break;
									case "tp_next_30_days"  :  break;
									case "tp_last_year"     :  break;
									case "tp_this_year"     :  break;
									case "tp_next_year"     :  break;
									case "changed"          :  break;
									case "unchanged"        :  break;
									case "increased"        :  break;
									case "decreased"        :  break;
									// 11/16/2008 Paul.  Days old 
									case "tp_minutes_after" :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_hours_after"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_days_after"    :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_weeks_after"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_months_after"  :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_years_after"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_minutes_before":  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_hours_before"  :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_days_before"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_weeks_before"  :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_months_before" :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_years_before"  :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									// 12/04/2008 Paul.  We need to be able to do an an equals. 
									case "tp_days_old"      :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_weeks_old"     :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_months_old"    :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
									case "tp_years_old"     :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								}
							}
							break;
						}
						case "int32":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "less"      :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "greater"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "between"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  txtFILTER_SEARCH_TEXT2.Text = sSEARCH_TEXT2;  break;
								case "not_equals":  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "decimal":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "less"      :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "greater"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "between"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  txtFILTER_SEARCH_TEXT2.Text = sSEARCH_TEXT2;  break;
								case "not_equals":  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "float":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "less"      :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "greater"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "between"   :  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  txtFILTER_SEARCH_TEXT2.Text = sSEARCH_TEXT2;  break;
								case "not_equals":  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT1;  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "bool":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :
									try
									{
										// 12/20/2006 Paul.  Catch and ignore the exception. 
										// 08/19/2010 Paul.  Check the list before assigning the value. 
										Utils.SetSelectedValue(lstFILTER_SEARCH_DROPDOWN, sSEARCH_TEXT1);
									}
									catch
									{
									}
									break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "guid":
						{
							switch ( sOPERATOR )
							{
								// 05/05/2010 Paul.  We store both the ID and the Name for a Guid IS. 
								case "is"            :  txtFILTER_SEARCH_ID  .Value = sSEARCH_TEXT1;  txtFILTER_SEARCH_TEXT.Text = sSEARCH_TEXT2;  break;
								case "equals"        :  txtFILTER_SEARCH_TEXT.Text  = sSEARCH_TEXT1;  break;
								case "contains"      :  txtFILTER_SEARCH_TEXT.Text  = sSEARCH_TEXT1;  break;
								case "starts_with"   :  txtFILTER_SEARCH_TEXT.Text  = sSEARCH_TEXT1;  break;
								case "ends_with"     :  txtFILTER_SEARCH_TEXT.Text  = sSEARCH_TEXT1;  break;
								case "not_equals_str":  txtFILTER_SEARCH_TEXT.Text  = sSEARCH_TEXT1;  break;
								case "empty"         :  break;
								case "not_empty"     :  break;
								case "one_of"        :
								{
									// 05/20/2009 Paul.  If this is a one-of guid, then populate the listbox user or team names. 
									foreach ( string s in arrSEARCH_TEXT )
									{
										for ( int i = 0; i < lstFILTER_SEARCH_LISTBOX.Items.Count; i++ )
										{
											if ( s == lstFILTER_SEARCH_LISTBOX.Items[i].Value )
												lstFILTER_SEARCH_LISTBOX.Items[i].Selected = true;
										}
									}
									break;
								}
							}
							break;
						}
						case "enum":
						{
							switch ( sOPERATOR )
							{
								case "is"            :
									try
									{
										// 12/20/2006 Paul.  Catch and ignore the exception. 
										// 08/19/2010 Paul.  Check the list before assigning the value. 
										Utils.SetSelectedValue(lstFILTER_SEARCH_DROPDOWN, sSEARCH_TEXT1);
									}
									catch
									{
									}
									break;
								case "one_of":
								{
									foreach ( string s in arrSEARCH_TEXT )
									{
										for ( int i = 0; i < lstFILTER_SEARCH_LISTBOX.Items.Count; i++ )
										{
											if ( s == lstFILTER_SEARCH_LISTBOX.Items[i].Value )
												lstFILTER_SEARCH_LISTBOX.Items[i].Selected = true;
										}
									}
									break;
								}
								case "empty"         :  break;
								case "not_empty"     :  break;
							}
							break;
						}
					}
					if ( Command != null )
						Command(sender, e);
				}
				else if ( e.CommandName == "Filters.Update" )
				{
					string sFILTER_ID    = txtFILTER_ID.Value;
					string sMODULE_NAME  = lstFILTER_COLUMN_SOURCE.SelectedValue;
					string sDATA_FIELD   = lstFILTER_COLUMN       .SelectedValue;
					string sDATA_TYPE    = txtFILTER_SEARCH_DATA_TYPE.Value;
					string sOPERATOR     = lstFILTER_OPERATOR     .SelectedValue;
					
					string[] arrSEARCH_TEXT = new string[0];
					// 07/06/2007 Paul.  ansistring is treated the same as string. 
					string sCOMMON_DATA_TYPE = sDATA_TYPE;
					if ( sCOMMON_DATA_TYPE == "ansistring" )
						sCOMMON_DATA_TYPE = "string";
					switch ( sCOMMON_DATA_TYPE )
					{
						case "string":
						{
							switch ( sOPERATOR )
							{
								case "equals"        :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "contains"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "starts_with"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "ends_with"     :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "not_equals_str":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "empty"         :  break;
								case "not_empty"     :  break;
							}
							break;
						}
						case "datetime":
						{
							switch ( sOPERATOR )
							{
								case "on"               :  arrSEARCH_TEXT = new string[] { Sql.ToDateTime(ctlFILTER_SEARCH_START_DATE.DateText).ToString("yyyy/MM/dd") };  break;
								case "before"           :  arrSEARCH_TEXT = new string[] { Sql.ToDateTime(ctlFILTER_SEARCH_START_DATE.DateText).ToString("yyyy/MM/dd") };  break;
								case "after"            :  arrSEARCH_TEXT = new string[] { Sql.ToDateTime(ctlFILTER_SEARCH_START_DATE.DateText).ToString("yyyy/MM/dd") };  break;
								case "not_equals_str"   :  arrSEARCH_TEXT = new string[] { Sql.ToDateTime(ctlFILTER_SEARCH_START_DATE.DateText).ToString("yyyy/MM/dd") };  break;
								case "between_dates"    :  arrSEARCH_TEXT = new string[] { Sql.ToDateTime(ctlFILTER_SEARCH_START_DATE.DateText).ToString("yyyy/MM/dd"), Sql.ToDateTime(ctlFILTER_SEARCH_END_DATE.DateText).ToString("yyyy/MM/dd") };  break;
								case "empty"            :  break;
								case "not_empty"        :  break;
								case "is_before"        :  break;
								case "is_after"         :  break;
								case "tp_yesterday"     :  break;
								case "tp_today"         :  break;
								case "tp_tomorrow"      :  break;
								case "tp_last_7_days"   :  break;
								case "tp_next_7_days"   :  break;
								case "tp_last_month"    :  break;
								case "tp_this_month"    :  break;
								case "tp_next_month"    :  break;
								case "tp_last_30_days"  :  break;
								case "tp_next_30_days"  :  break;
								case "tp_last_year"     :  break;
								case "tp_this_year"     :  break;
								case "tp_next_year"     :  break;
								case "changed"          :  break;
								case "unchanged"        :  break;
								case "increased"        :  break;
								case "decreased"        :  break;
								case "tp_minutes_after" :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_hours_after"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_days_after"    :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_weeks_after"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_months_after"  :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_years_after"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_minutes_before":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_hours_before"  :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_days_before"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_weeks_before"  :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_months_before" :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_years_before"  :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								// 12/04/2008 Paul.  We need to be able to do an an equals. 
								case "tp_days_old"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_weeks_old"     :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_months_old"    :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "tp_years_old"     :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
							}
							break;
						}
						case "int32":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "less"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "greater"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "between"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text, txtFILTER_SEARCH_TEXT2.Text };  break;
								case "not_equals":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "decimal":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "less"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "greater"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "between"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text, txtFILTER_SEARCH_TEXT2.Text };  break;
								case "not_equals":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "float":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "less"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "greater"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "between"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text, txtFILTER_SEARCH_TEXT2.Text };  break;
								case "not_equals":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "bool":
						{
							switch ( sOPERATOR )
							{
								case "equals"    :  arrSEARCH_TEXT = new string[] { lstFILTER_SEARCH_DROPDOWN.SelectedValue };  break;
								case "empty"     :  break;
								case "not_empty" :  break;
							}
							break;
						}
						case "guid":
						{
							switch ( sOPERATOR )
							{
								// 05/05/2010 Paul.  We store both the ID and the Name for a Guid IS. 
								// 05/05/2010 Paul.  Since the txtFILTER_SEARCH_TEXT field is ReadOnly, .NET will not get the value. 
								// The submitted value is still available from the Request object. 
								case "is"            :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_ID.Value , Sql.ToString(Request[txtFILTER_SEARCH_TEXT.UniqueID]) };  break;
								case "equals"        :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "contains"      :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "starts_with"   :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "ends_with"     :  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "not_equals_str":  arrSEARCH_TEXT = new string[] { txtFILTER_SEARCH_TEXT.Text };  break;
								case "empty"         :  break;
								case "not_empty"     :  break;
								// 05/20/2009 Paul.  If this is a one-of guid, then populate the listbox user or team names. 
								case "one_of"        :  arrSEARCH_TEXT = Sql.ToStringArray(lstFILTER_SEARCH_LISTBOX);  break;
							}
							break;
						}
						case "enum":
						{
							switch ( sOPERATOR )
							{
								case "is"            :  arrSEARCH_TEXT = new string[] { lstFILTER_SEARCH_DROPDOWN.SelectedValue };  break;
								case "one_of"        :  arrSEARCH_TEXT = Sql.ToStringArray(lstFILTER_SEARCH_LISTBOX);  break;
								case "empty"         :  break;
								case "not_empty"     :  break;
							}
							break;
						}
					}
					FiltersUpdate(sFILTER_ID, sMODULE_NAME, sDATA_FIELD, sDATA_TYPE, sOPERATOR, arrSEARCH_TEXT );
					BuildReportSQL();
					ResetSearchText();
					if ( Command != null )
						Command(sender, e);
				}
				else if ( e.CommandName == "Cancel" )
				{
					Response.Redirect("default.aspx");
				}
			}
			catch(Exception ex)
			{
				lblError.Text = ex.Message;
			}
		}
		#endregion

		#region Changed
		protected void lstMODULE_Changed(Object sender, EventArgs e)
		{
			lblMODULE.Text = lstMODULE.SelectedValue;
			// 05/26/2006 Paul.  If the module changes, then throw away everything. 
			// The display columns don't count, the group columns don't count, etc. 
			rdl = new RdlDocument(String.Empty, String.Empty);
			rdl.SetCustomProperty("Module"        , lstMODULE.SelectedValue );
			rdl.SetCustomProperty("Related"       , String.Empty);
			rdl.SetCustomProperty("RelatedModules", String.Empty);
			rdl.SetCustomProperty("Relationships" , String.Empty);
			rdl.SetCustomProperty("Filters"       , String.Empty);
			lstRELATED_Bind();
			lblRELATED.Text = lstRELATED.SelectedValue;
			dgFilters.DataSource = ReportFilters();
			dgFilters.DataBind();
			BuildReportSQL();
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "Module");
				Command(sender, args);
			}
		}

		protected void lstRELATED_Changed(Object sender, EventArgs e)
		{
			lblRELATED.Text = lstRELATED.SelectedValue;
			rdl.SetCustomProperty("Related"      , lstRELATED.SelectedValue);
			rdl.SetCustomProperty("Relationships", String.Empty);
			lstFILTER_COLUMN_SOURCE_Bind();
			// 06/13/2006 Paul.  If the related module changes, then make sure to remove any unavailable filters. 
			RemoveInvalidFilters();
			// 07/13/2006 Paul.  Remove invalid display columns as well. 
			RemoveInvalidDisplayColumns();
			BuildReportSQL();
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "Related");
				Command(sender, args);
			}
		}

		protected void lstMODULE_COLUMN_SOURCE_Changed(Object sender, EventArgs e)
		{
			lblMODULE_COLUMN_SOURCE.Text = lstMODULE_COLUMN_SOURCE.SelectedValue;
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "ModuleColumnSource");
				Command(sender, args);
			}
		}

		protected void lstFILTER_COLUMN_SOURCE_Changed(Object sender, EventArgs e)
		{
			lblFILTER_COLUMN_SOURCE.Text = lstFILTER_COLUMN_SOURCE.SelectedValue;
			lstFILTER_COLUMN_Bind();
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "FilterColumnSource");
				Command(sender, args);
			}
		}

		protected void lstFILTER_COLUMN_Changed(Object sender, EventArgs e)
		{
			lblFILTER_COLUMN.Text = lstFILTER_COLUMN.SelectedValue;
			lblFILTER_OPERATOR_Bind();
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "FilterColumn");
				Command(sender, args);
			}
		}

		protected void lstFILTER_OPERATOR_Changed(Object sender, EventArgs e)
		{
			lblFILTER_OPERATOR.Text = lstFILTER_OPERATOR.SelectedValue;
			BindSearchText();
			if ( Command != null )
			{
				CommandEventArgs args = new CommandEventArgs("Filters.Change", "FilterOperator");
				Command(sender, args);
			}
		}
		#endregion

		#region Bind
		private void lstRELATED_Bind()
		{
			DataView vwRelationships = new DataView(SplendidCache.ReportingRelationships());
			vwRelationships.RowFilter = "       RELATIONSHIP_TYPE = 'many-to-many' " + ControlChars.CrLf
			                          + "   and LHS_MODULE        = \'" + lstMODULE.SelectedValue + "\'" + ControlChars.CrLf;
			// 06/10/2006 Paul.  Filter by the modules that the user has access to. 
			Sql.AppendParameter(vwRelationships, SplendidCache.ReportingModulesList(), "RHS_MODULE", false);

			XmlDocument xmlRelationships = new XmlDocument();
			xmlRelationships.AppendChild(xmlRelationships.CreateElement("Relationships"));
			
			XmlNode xRelationship = null;
			foreach(DataRowView row in vwRelationships)
			{
				string sRELATIONSHIP_NAME              = Sql.ToString(row["RELATIONSHIP_NAME"             ]);
				string sLHS_MODULE                     = Sql.ToString(row["LHS_MODULE"                    ]);
				string sLHS_TABLE                      = Sql.ToString(row["LHS_TABLE"                     ]).ToUpper();
				string sLHS_KEY                        = Sql.ToString(row["LHS_KEY"                       ]).ToUpper();
				string sRHS_MODULE                     = Sql.ToString(row["RHS_MODULE"                    ]);
				string sRHS_TABLE                      = Sql.ToString(row["RHS_TABLE"                     ]).ToUpper();
				string sRHS_KEY                        = Sql.ToString(row["RHS_KEY"                       ]).ToUpper();
				string sJOIN_TABLE                     = Sql.ToString(row["JOIN_TABLE"                    ]).ToUpper();
				string sJOIN_KEY_LHS                   = Sql.ToString(row["JOIN_KEY_LHS"                  ]).ToUpper();
				string sJOIN_KEY_RHS                   = Sql.ToString(row["JOIN_KEY_RHS"                  ]).ToUpper();
				// 11/20/2008 Paul.  Quotes, Orders and Invoices have a relationship column. 
				string sRELATIONSHIP_ROLE_COLUMN       = Sql.ToString(row["RELATIONSHIP_ROLE_COLUMN"      ]).ToUpper();
				string sRELATIONSHIP_ROLE_COLUMN_VALUE = Sql.ToString(row["RELATIONSHIP_ROLE_COLUMN_VALUE"]);
				string sMODULE_NAME       = sRHS_MODULE + " " + sRHS_TABLE;
				string sDISPLAY_NAME      = L10n.Term(".moduleList." + sRHS_MODULE);
				if ( bDebug )
				{
					sDISPLAY_NAME = "[" + sMODULE_NAME + "] " + sDISPLAY_NAME;
				}
				// 02/18/2009 Paul.  Include the relationship column if provided. 
				//if ( !Sql.IsEmptyString(sRELATIONSHIP_ROLE_COLUMN) && !Sql.IsEmptyString(sRELATIONSHIP_ROLE_COLUMN_VALUE) && sRELATIONSHIP_ROLE_COLUMN_VALUE != lstMODULE.SelectedValue )
				//	sDISPLAY_NAME += " " + sRELATIONSHIP_ROLE_COLUMN_VALUE;
				
				xRelationship = xmlRelationships.CreateElement("Relationship");
				xmlRelationships.DocumentElement.AppendChild(xRelationship);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_NAME"             , sRELATIONSHIP_NAME             );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_MODULE"                    , sLHS_MODULE                    );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_TABLE"                     , sLHS_TABLE                     );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_KEY"                       , sLHS_KEY                       );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_MODULE"                    , sRHS_MODULE                    );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_TABLE"                     , sRHS_TABLE                     );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_KEY"                       , sRHS_KEY                       );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "JOIN_TABLE"                    , sJOIN_TABLE                    );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "JOIN_KEY_LHS"                  , sJOIN_KEY_LHS                  );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "JOIN_KEY_RHS"                  , sJOIN_KEY_RHS                  );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_TYPE"             , "many-to-many"                 );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_NAME"                   , sMODULE_NAME                   );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"                  , sDISPLAY_NAME                  );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_ROLE_COLUMN"      , sRELATIONSHIP_ROLE_COLUMN      );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_ROLE_COLUMN_VALUE", sRELATIONSHIP_ROLE_COLUMN_VALUE);
			}
			rdl.SetCustomProperty("RelatedModules", xmlRelationships.OuterXml.Replace("</Relationship>", "</Relationship>" + ControlChars.CrLf));

			DataTable dtModules = XmlUtil.CreateDataTable(xmlRelationships.DocumentElement, "Relationship", new string[] {"MODULE_NAME", "DISPLAY_NAME"});
			DataView vwModules = new DataView(dtModules);
			vwModules.Sort = "DISPLAY_NAME";
			lstRELATED.DataSource = vwModules;
			lstRELATED.DataBind();
			lstRELATED.Items.Insert(0, new ListItem(L10n.Term(".LBL_NONE"), ""));
			
			lstFILTER_COLUMN_SOURCE_Bind();
		}

		private void lstFILTER_COLUMN_SOURCE_Bind()
		{
			// 07/13/2006 Paul.  Convert the module name to the correct table name. 
			string sModule = lstMODULE.SelectedValue;
			DataView vwRelationships = new DataView(SplendidCache.ReportingRelationships());
			vwRelationships.RowFilter = "       RELATIONSHIP_TYPE = 'one-to-many' " + ControlChars.CrLf
			                          + "   and RHS_MODULE        = \'" + sModule + "\'" + ControlChars.CrLf;
			// 06/10/2006 Paul.  Filter by the modules that the user has access to. 
			Sql.AppendParameter(vwRelationships, SplendidCache.ReportingModulesList(), "RHS_MODULE", false);
			vwRelationships.Sort = "RHS_KEY";


			XmlDocument xmlRelationships = new XmlDocument();
			xmlRelationships.AppendChild(xmlRelationships.CreateElement("Relationships"));
			
			XmlNode xRelationship = xmlRelationships.CreateElement("Relationship");
			xmlRelationships.DocumentElement.AppendChild(xRelationship);

			string sMODULE_TABLE = Sql.ToString(Application["Modules." + sModule + ".TableName"]);
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_NAME", sModule      );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_MODULE"       , sModule      );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_TABLE"        , sMODULE_TABLE);
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_KEY"          , String.Empty );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_MODULE"       , String.Empty );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_TABLE"        , String.Empty );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_KEY"          , String.Empty );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_TYPE", String.Empty );
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_ALIAS"     , sMODULE_TABLE);
			XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_NAME"      , sModule + " " + sMODULE_TABLE);
			// 07/29/2008 Paul.  The module name needs to be translated as it will be used in the field headers. 
			if ( bDebug )
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"     , "[" + L10n.Term(".moduleList." + sModule) + " " + sMODULE_TABLE + "] " + sModule);
			else
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"     , L10n.Term(".moduleList." + sModule));
			
			foreach(DataRowView row in vwRelationships)
			{
				string sRELATIONSHIP_NAME = Sql.ToString(row["RELATIONSHIP_NAME"]);
				string sLHS_MODULE        = Sql.ToString(row["LHS_MODULE"       ]);
				string sLHS_TABLE         = Sql.ToString(row["LHS_TABLE"        ]).ToUpper();
				string sLHS_KEY           = Sql.ToString(row["LHS_KEY"          ]).ToUpper();
				string sRHS_MODULE        = Sql.ToString(row["RHS_MODULE"       ]);
				string sRHS_TABLE         = Sql.ToString(row["RHS_TABLE"        ]).ToUpper();
				string sRHS_KEY           = Sql.ToString(row["RHS_KEY"          ]).ToUpper();
				// 07/13/2006 Paul.  It may seem odd the way we are combining LHS_TABLE and RHS_KEY,  but we do it this way for a reason.  
				// The table alias to get to an Email Assigned User ID will be USERS_ASSIGNED_USER_ID. 
				string sMODULE_NAME       = sLHS_MODULE + " " + sLHS_TABLE + "_" + sRHS_KEY;
				string sDISPLAY_NAME      = sRHS_MODULE;
				
				// 07/09/2007 Paul.  Fixes from Version 1.2 on 04/17/2007 were not included in Version 1.4 tree.
				switch ( sRHS_KEY.ToUpper() )
				{
					// 04/17/2007 Paul.  CREATED_BY was renamed CREATED_BY_ID in all views a long time ago. It is just now being fixed here. 
					case "CREATED_BY_ID":
						sDISPLAY_NAME = L10n.Term(".moduleList." + sRHS_MODULE) + ": " + L10n.Term(".LBL_CREATED_BY_USER");
						break;
					case "MODIFIED_USER_ID":
						sDISPLAY_NAME = L10n.Term(".moduleList." + sRHS_MODULE) + ": " + L10n.Term(".LBL_MODIFIED_BY_USER");
						break;
					case "ASSIGNED_USER_ID":
						sDISPLAY_NAME = L10n.Term(".moduleList." + sRHS_MODULE) + ": " + L10n.Term(".LBL_ASSIGNED_TO_USER");
						break;
					// 04/17/2007 Paul.  PARENT_ID is a special case where we want to know the type of the parent. 
					case "PARENT_ID":
						sDISPLAY_NAME = L10n.Term(".moduleList." + sRHS_MODULE) + ": " + L10n.Term(".moduleList." + sLHS_MODULE) + " " + L10n.Term(".LBL_PARENT_ID");
						break;
					default:
						sDISPLAY_NAME = L10n.Term(".moduleList." + sRHS_MODULE) + ": " + Utils.TableColumnName(L10n, sRHS_MODULE, sRHS_KEY);
						break;
				}
				if ( bDebug )
				{
					sDISPLAY_NAME = "[" + sMODULE_NAME + "] " + sDISPLAY_NAME;
				}
				
				xRelationship = xmlRelationships.CreateElement("Relationship");
				xmlRelationships.DocumentElement.AppendChild(xRelationship);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_NAME", sRELATIONSHIP_NAME);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_MODULE"       , sLHS_MODULE       );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_TABLE"        , sLHS_TABLE        );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_KEY"          , sLHS_KEY          );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_MODULE"       , sRHS_MODULE       );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_TABLE"        , sRHS_TABLE        );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_KEY"          , sRHS_KEY          );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_TYPE", "one-to-many"     );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_ALIAS"     , sLHS_TABLE + "_" + sRHS_KEY);  // This is just the alias. 
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_NAME"      , sMODULE_NAME      );  // Module name includes the alias. 
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"     , sDISPLAY_NAME     );
			}
			if ( !Sql.IsEmptyString(lstRELATED.SelectedValue) )
			{
				xRelationship = xmlRelationships.CreateElement("Relationship");
				xmlRelationships.DocumentElement.AppendChild(xRelationship);
				string sRELATED_MODULE = lstRELATED.SelectedValue.Split(' ')[0];
				string sRELATED_ALIAS  = lstRELATED.SelectedValue.Split(' ')[1];
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_NAME", sRELATED_MODULE);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_MODULE"       , sRELATED_MODULE);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_TABLE"        , sRELATED_ALIAS );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "LHS_KEY"          , String.Empty      );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_MODULE"       , String.Empty      );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_TABLE"        , String.Empty      );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RHS_KEY"          , String.Empty      );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "RELATIONSHIP_TYPE", "many-to-many"    );
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_ALIAS"     , sRELATED_ALIAS);
				XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "MODULE_NAME"      , sRELATED_MODULE + " " + sRELATED_ALIAS);
				// 07/29/2008 Paul.  The module name needs to be translated as it will be used in the field headers. 
				if ( bDebug )
					XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"     , "[" + L10n.Term(".moduleList." + sRELATED_MODULE) + " " + sRELATED_ALIAS + "] " + sRELATED_MODULE);
				else
					XmlUtil.SetSingleNode(xmlRelationships, xRelationship, "DISPLAY_NAME"     , L10n.Term(".moduleList." + sRELATED_MODULE));
			}
			rdl.SetCustomProperty("Relationships", xmlRelationships.OuterXml.Replace("</Relationship>", "</Relationship>" + ControlChars.CrLf));

			DataTable dtModuleColumnSource = XmlUtil.CreateDataTable(xmlRelationships.DocumentElement, "Relationship", new string[] {"MODULE_NAME", "DISPLAY_NAME"});
			lstMODULE_COLUMN_SOURCE.DataSource = dtModuleColumnSource;
			lstMODULE_COLUMN_SOURCE.DataBind();
			lblMODULE_COLUMN_SOURCE.Text = lstMODULE_COLUMN_SOURCE.SelectedValue;
			// 05/29/2006 Paul.  Filter column source is always the same as module column source. 
			lstFILTER_COLUMN_SOURCE.DataSource = dtModuleColumnSource;
			lstFILTER_COLUMN_SOURCE.DataBind();
			lblFILTER_COLUMN_SOURCE.Text = lstFILTER_COLUMN_SOURCE.SelectedValue;

			lstFILTER_COLUMN_Bind();
		}

		private void lstFILTER_COLUMN_Bind()
		{
			lstFILTER_COLUMN.DataSource = null;
			lstFILTER_COLUMN.DataBind();

			string[] arrModule = lstFILTER_COLUMN_SOURCE.SelectedValue.Split(' ');
			string sModule     = arrModule[0];
			string sTableAlias = arrModule[1];

			string sMODULE_TABLE = Sql.ToString(Application["Modules." + sModule + ".TableName"]);
			DataTable dtColumns = SplendidCache.ReportingFilterColumns(sMODULE_TABLE).Copy();
			foreach(DataRow row in dtColumns.Rows)
			{
				row["NAME"        ] = sTableAlias + "." + Sql.ToString(row["NAME"]);
				// 07/04/2006 Paul.  Some columns have global terms. 
				row["DISPLAY_NAME"] = Utils.TableColumnName(L10n, sModule, Sql.ToString(row["DISPLAY_NAME"]));
			}
			ViewState["FILTER_COLUMNS"] = dtColumns;
			
			// 06/21/2006 Paul.  Do not sort the columns  We want it to remain sorted by COLID. This should keep the NAME at the top. 
			DataView vwColumns = new DataView(dtColumns);
			lstFILTER_COLUMN.DataSource = vwColumns;
			lstFILTER_COLUMN.DataBind();
			lblFILTER_COLUMN.Text = lstFILTER_COLUMN.SelectedValue;

			lblFILTER_OPERATOR_Bind();
		}

		private void lblFILTER_OPERATOR_Bind()
		{
			lstFILTER_OPERATOR.DataSource = null;
			lstFILTER_OPERATOR.Items.Clear();

			string[] arrModule = lstFILTER_COLUMN_SOURCE.SelectedValue.Split(' ');
			string sModule     = arrModule[0];
			string sTableAlias = arrModule[1];
			
			string[] arrColumn = lstFILTER_COLUMN.SelectedValue.Split('.');
			string sColumnName = arrColumn[0];
			if ( arrColumn.Length > 1 )
				sColumnName = arrColumn[1];
			
			string sMODULE_TABLE = Sql.ToString(Application["Modules." + sModule + ".TableName"]);
			DataView vwColumns = new DataView(SplendidCache.ReportingFilterColumns(sMODULE_TABLE).Copy());
			vwColumns.RowFilter = "ColumnName = '" + sColumnName + "'";
			
			if ( vwColumns.Count > 0 )
			{
				DataRowView row = vwColumns[0];
				string sCsType = Sql.ToString(row["CsType"]);
				lblFILTER_OPERATOR_TYPE.Text = sCsType.ToLower();
				txtFILTER_SEARCH_DATA_TYPE.Value = sCsType.ToLower();
				
				lstFILTER_OPERATOR.DataSource = SplendidCache.List(sCsType.ToLower() + "_operator_dom");
				lstFILTER_OPERATOR.DataBind();
				lblFILTER_OPERATOR.Text = lstFILTER_OPERATOR.SelectedValue;
			}
			BindSearchText();
		}

		private void BindSearchText()
		{
			// 10/22/2008 Paul.  Make sure to clear the ReadOnly flag that may have been set on the previous operator change. 
			txtFILTER_SEARCH_TEXT      .ReadOnly = false;
			txtFILTER_SEARCH_TEXT      .Visible = false;
			txtFILTER_SEARCH_TEXT2     .Visible = false;
			lstFILTER_SEARCH_LISTBOX   .Visible = false;
			lstFILTER_SEARCH_DROPDOWN  .Visible = false;
			ctlFILTER_SEARCH_START_DATE.Visible = false;
			ctlFILTER_SEARCH_END_DATE  .Visible = false;
			lblFILTER_AND_SEPARATOR    .Visible = false;
			btnFILTER_SEARCH_SELECT    .Visible = false;
			// 02/09/2007 Paul.  Clear the lookups if not used. 
			if ( txtFILTER_SEARCH_DATA_TYPE.Value != "enum" )
			{
				lstFILTER_SEARCH_DROPDOWN.DataSource = null;
				lstFILTER_SEARCH_LISTBOX .DataSource = null;
				lstFILTER_SEARCH_DROPDOWN.DataBind();
				lstFILTER_SEARCH_LISTBOX .DataBind();
			}
			// 07/06/2007 Paul.  ansistring is treated the same as string. 
			string sCOMMON_DATA_TYPE = txtFILTER_SEARCH_DATA_TYPE.Value;
			if ( sCOMMON_DATA_TYPE == "ansistring" )
				sCOMMON_DATA_TYPE = "string";
			switch ( sCOMMON_DATA_TYPE )
			{
				case "string":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "equals"        :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "contains"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "starts_with"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "ends_with"     :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "not_equals_str":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "empty"         :  break;
						case "not_empty"     :  break;
					}
					break;
				}
				case "datetime":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "on"               :  ctlFILTER_SEARCH_START_DATE.Visible = true;  break;
						case "before"           :  ctlFILTER_SEARCH_START_DATE.Visible = true;  break;
						case "after"            :  ctlFILTER_SEARCH_START_DATE.Visible = true;  break;
						case "between_dates"    :  ctlFILTER_SEARCH_START_DATE.Visible = true;  lblFILTER_AND_SEPARATOR.Visible = true;  ctlFILTER_SEARCH_END_DATE.Visible = true;  break;
						case "not_equals_str"   :  ctlFILTER_SEARCH_START_DATE.Visible = true;  break;
						case "empty"            :  break;
						case "not_empty"        :  break;
						case "is_before"        :  break;
						case "is_after"         :  break;
						case "tp_yesterday"     :  break;
						case "tp_today"         :  break;
						case "tp_tomorrow"      :  break;
						case "tp_last_7_days"   :  break;
						case "tp_next_7_days"   :  break;
						case "tp_last_month"    :  break;
						case "tp_this_month"    :  break;
						case "tp_next_month"    :  break;
						case "tp_last_30_days"  :  break;
						case "tp_next_30_days"  :  break;
						case "tp_last_year"     :  break;
						case "tp_this_year"     :  break;
						case "tp_next_year"     :  break;
						case "changed"          :  break;
						case "unchanged"        :  break;
						case "increased"        :  break;
						case "decreased"        :  break;
						case "tp_minutes_after" :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_hours_after"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_days_after"    :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_weeks_after"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_months_after"  :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_years_after"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_minutes_before":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_hours_before"  :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_days_before"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_weeks_before"  :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_months_before" :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_years_before"  :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						// 12/04/2008 Paul.  We need to be able to do an an equals. 
						case "tp_days_old"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_weeks_old"     :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_months_old"    :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "tp_years_old"     :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
					}
					break;
				}
				case "int32":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "equals"    :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "less"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "greater"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "between"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  lblFILTER_AND_SEPARATOR.Visible = true;  txtFILTER_SEARCH_TEXT2.Visible = true ;  break;
						case "not_equals":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "empty"     :  break;
						case "not_empty" :  break;
					}
					break;
				}
				case "decimal":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "equals"    :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "less"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "greater"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "between"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  lblFILTER_AND_SEPARATOR.Visible = true;  txtFILTER_SEARCH_TEXT2.Visible = true ;  break;
						case "not_equals":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "empty"     :  break;
						case "not_empty" :  break;
					}
					break;
				}
				case "float":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "equals"    :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "less"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "greater"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "between"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  lblFILTER_AND_SEPARATOR.Visible = true;  txtFILTER_SEARCH_TEXT2.Visible = true ;  break;
						case "not_equals":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "empty"     :  break;
						case "not_empty" :  break;
					}
					break;
				}
				case "bool":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						case "equals"    :
							// 12/20/2006 Paul.  We need to populate the dropdown for booleans with 1 and 0. 
							lstFILTER_SEARCH_DROPDOWN.Visible = true ;
							lstFILTER_SEARCH_DROPDOWN.Items.Clear();
							lstFILTER_SEARCH_DROPDOWN.Items.Add(new ListItem(L10n.Term(".LBL_YES"), "1"));
							lstFILTER_SEARCH_DROPDOWN.Items.Add(new ListItem(L10n.Term(".LBL_NO" ), "0"));
							break;
						case "empty"     :  break;
						case "not_empty" :  break;
					}
					break;
				}
				case "guid":
				{
					switch ( lstFILTER_OPERATOR.SelectedValue )
					{
						// 05/05/2010 Paul.  The Select button was not being made visible. 
						case "is"            :
						{
							string[] arrModule = lstFILTER_COLUMN_SOURCE.SelectedValue.Split(' ');
							string sModule = arrModule[0];
							
							string[] arrColumn = lstFILTER_COLUMN.SelectedValue.Split('.');
							string sColumnName = arrColumn[0];
							if ( arrColumn.Length > 1 )
								sColumnName = arrColumn[1];
							
							string sMODULE_TYPE = String.Empty;
							switch ( sColumnName )
							{
								case "ID"              :  sMODULE_TYPE = sModule;  break;
								case "CREATED_BY_ID"   :  sMODULE_TYPE = "Users";  break;
								case "MODIFIED_USER_ID":  sMODULE_TYPE = "Users";  break;
								case "ASSIGNED_USER_ID":  sMODULE_TYPE = "Users";  break;
								case "TEAM_ID"         :  sMODULE_TYPE = "Teams";  break;
							}
							txtFILTER_SEARCH_TEXT.Visible   = true;
							txtFILTER_SEARCH_TEXT.ReadOnly  = true;
							btnFILTER_SEARCH_SELECT.Visible = true;
							if ( !Sql.IsEmptyString(sMODULE_TYPE) )
								btnFILTER_SEARCH_SELECT.OnClientClick = "return ModulePopup('" + sMODULE_TYPE + "', '" + txtFILTER_SEARCH_ID.ClientID + "', '" + txtFILTER_SEARCH_TEXT.ClientID + "', null, false, null);";
							break;
						}
						case "equals"        :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "contains"      :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "starts_with"   :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "ends_with"     :  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "not_equals_str":  txtFILTER_SEARCH_TEXT.Visible = true ;  break;
						case "empty"         :  break;
						case "not_empty"     :  break;
						case "one_of"        :
						{
							// 05/20/2009 Paul.  If this is a one-of guid, then populate the listbox user or team names. 
							string[] arrModule = lstFILTER_COLUMN_SOURCE.SelectedValue.Split(' ');
							string sModule = arrModule[0];
							
							string[] arrColumn = lstFILTER_COLUMN.SelectedValue.Split('.');
							string sColumnName = arrColumn[0];
							if ( arrColumn.Length > 1 )
								sColumnName = arrColumn[1];
							
							string sLIST_NAME = String.Empty;
							switch ( sColumnName )
							{
								case "CREATED_BY_ID"   :  sLIST_NAME = "AssignedUser";  break;
								case "MODIFIED_USER_ID":  sLIST_NAME = "AssignedUser";  break;
								case "ASSIGNED_USER_ID":  sLIST_NAME = "AssignedUser";  break;
								case "TEAM_ID"         :  sLIST_NAME = "Teams"       ;  break;
							}
							if ( Sql.IsEmptyString(sLIST_NAME) )
							{
								lstFILTER_SEARCH_LISTBOX .DataSource = null;
								lstFILTER_SEARCH_LISTBOX .DataBind();
							}
							else
							{
								SplendidCacheReference[] arrCustomCaches = SplendidCache.CustomCaches;
								foreach ( SplendidCacheReference cache in arrCustomCaches )
								{
									if ( cache.Name == sLIST_NAME )
									{
										SplendidCacheCallback cbkDataSource = cache.DataSource;
										lstFILTER_SEARCH_LISTBOX.DataValueField = cache.DataValueField;
										lstFILTER_SEARCH_LISTBOX.DataTextField  = cache.DataTextField ;
										lstFILTER_SEARCH_LISTBOX.DataSource     = cbkDataSource();
										lstFILTER_SEARCH_LISTBOX.DataBind();
										lstFILTER_SEARCH_LISTBOX.Visible = true;
										break;
									}
								}
							}
							break;
						}
					}
					break;
				}
				case "enum":
				{
					// 02/09/2007 Paul.  If this is an enum, then populate the listbox with list names pulled from EDITVIEWS_FIELDS.
					string[] arrModule = lstFILTER_COLUMN_SOURCE.SelectedValue.Split(' ');
					string sModule = arrModule[0];
					
					string[] arrColumn = lstFILTER_COLUMN.SelectedValue.Split('.');
					string sColumnName = arrColumn[0];
					if ( arrColumn.Length > 1 )
						sColumnName = arrColumn[1];
					
					string sMODULE_TABLE = Sql.ToString(Application["Modules." + sModule + ".TableName"]);
					string sLIST_NAME = SplendidCache.ReportingFilterColumnsListName(sMODULE_TABLE, sColumnName);
					if ( Sql.IsEmptyString(sLIST_NAME) )
					{
						lstFILTER_SEARCH_DROPDOWN.DataSource = null;
						lstFILTER_SEARCH_LISTBOX .DataSource = null;
						lstFILTER_SEARCH_DROPDOWN.DataBind();
						lstFILTER_SEARCH_LISTBOX .DataBind();
					}
					else
					{
						lstFILTER_SEARCH_DROPDOWN.DataSource = SplendidCache.List(sLIST_NAME);
						lstFILTER_SEARCH_DROPDOWN.DataBind();
						// 05/20/2009 Paul.  We need to restore the field names as they may have changed in Guid one_of. 
						lstFILTER_SEARCH_LISTBOX.DataValueField = "NAME";
						lstFILTER_SEARCH_LISTBOX.DataTextField  = "DISPLAY_NAME";
						lstFILTER_SEARCH_LISTBOX .DataSource = lstFILTER_SEARCH_DROPDOWN.DataSource;
						lstFILTER_SEARCH_LISTBOX .DataBind();
						switch ( lstFILTER_OPERATOR.SelectedValue )
						{
							case "is"            :  lstFILTER_SEARCH_DROPDOWN.Visible = true;  break;
							case "one_of"        :  lstFILTER_SEARCH_LISTBOX .Visible = true;  break;
							case "empty"         :  break;
							case "not_empty"     :  break;
						}
					}
					break;
				}
			}
		}
		#endregion

		// 04/17/2007 Paul.  We need to apply ACL rules a little different from the standard.
		// 07/09/2007 Paul.  Fixes from Version 1.2 on 04/17/2007 were not included in Version 1.4 tree.
		public static void ACLFilter(StringBuilder sbJoin, StringBuilder sbWhere, string sMODULE_NAME, string sACCESS_TYPE, string sASSIGNED_USER_ID_Field, bool bIsCaseSignificantDB)
		{
			// 12/07/2006 Paul.  Not all views use ASSIGNED_USER_ID as the assigned field.  Allow an override. 
			// 11/25/2006 Paul.  Administrators should not be restricted from seeing items because of the team rights.
			// This is so that an administrator can fix any record with a bad team value. 
			// 11/27/2009 Paul.  We need a dynamic way to determine if the module record can be assigned or placed in a team. 
			// Teamed and Assigned flags are automatically determined based on the existence of TEAM_ID and ASSIGNED_USER_ID fields. 
			bool bModuleIsTeamed        = Sql.ToBoolean(HttpContext.Current.Application["Modules." + sMODULE_NAME + ".Teamed"  ]);
			bool bModuleIsAssigned      = Sql.ToBoolean(HttpContext.Current.Application["Modules." + sMODULE_NAME + ".Assigned"]);
			bool bEnableTeamManagement  = Crm.Config.enable_team_management();
			bool bRequireTeamManagement = Crm.Config.require_team_management();
			bool bRequireUserAssignment = Crm.Config.require_user_assignment();
			// 11/27/2009 Paul.  Allow dynamic teams to be turned off. 
			bool bEnableDynamicTeams    = Crm.Config.enable_dynamic_teams();
			bool bIsAdmin = Security.IS_ADMIN;
			// 11/27/2009 Paul.  Don't apply admin rules when debugging so that we can test the code. 
#if DEBUG
			bIsAdmin = false;
#endif
			if ( bModuleIsTeamed )
			{
				// 02/10/2008 Kerry.  Remove debug code to force non-admin. 
				if ( bIsAdmin )
					bRequireTeamManagement = false;

				DbProviderFactory dbf = DbProviderFactories.GetFactory();
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					if ( bEnableTeamManagement )
					{
						if ( bEnableDynamicTeams )
						{
							// 08/31/2009 Paul.  Dynamic Teams are handled just like regular teams except using a different view. 
							if ( bRequireTeamManagement )
								sbJoin.Append("       inner ");
							else
								sbJoin.Append("  left outer ");
							// 11/27/2009 Paul.  Use Sql.MetadataName() so that the view name can exceed 30 characters, but still be truncated for Oracle. 
							// 11/27/2009 Paul.  vwTEAM_SET_MEMBERSHIPS_Security has a distinct clause to reduce duplicate rows. 
							// 12/07/2009 Paul.  Must include the module when referencing the TEAM_SET_ID. 
							// 03/08/2011 Paul.  We need to make sure not to exceed 30 characters in the alias name. 
							sbJoin.AppendLine("join " + Sql.MetadataName(con, "vwTEAM_SET_MEMBERSHIPS_Security") + " " + Sql.MetadataName(con, "vwTEAM_SET_MEMBERSHIPS_" + sMODULE_NAME) + ControlChars.CrLf);
							sbJoin.AppendLine("               on " + Sql.MetadataName(con, "vwTEAM_SET_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_TEAM_SET_ID = " + sMODULE_NAME + ".TEAM_SET_ID" + ControlChars.CrLf);
							// 05/05/2010 Paul.  We need to hard-code the value of the MEMBERSHIP_USER_ID as there is no practical way to use a runtime-value. 
							sbJoin.AppendLine("              and " + Sql.MetadataName(con, "vwTEAM_SET_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_USER_ID     = '" + Security.USER_ID.ToString() + "'" + ControlChars.CrLf);
						}
						else
						{
							if ( bRequireTeamManagement )
								sbJoin.Append("       inner ");
							else
								sbJoin.Append("  left outer ");
							// 03/08/2011 Paul.  We need to make sure not to exceed 30 characters in the alias name. 
							sbJoin.AppendLine("join vwTEAM_MEMBERSHIPS  " + Sql.MetadataName(con, "vwTEAM_MEMBERSHIPS_" + sMODULE_NAME));
							sbJoin.AppendLine("               on " + Sql.MetadataName(con, "vwTEAM_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_TEAM_ID = " + sMODULE_NAME + ".TEAM_ID");
							// 05/05/2010 Paul.  We need to hard-code the value of the MEMBERSHIP_USER_ID as there is no practical way to use a runtime-value. 
							sbJoin.AppendLine("              and " + Sql.MetadataName(con, "vwTEAM_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_USER_ID = '" + Security.USER_ID.ToString() + "'");
							//Sql.AddParameter(cmd, "@MEMBERSHIP_USER_ID", Security.USER_ID);
						}
					}

					if ( bEnableTeamManagement && !bRequireTeamManagement && !bIsAdmin )
					{
						// 11/27/2009 Paul.  Dynamic Teams are handled just like regular teams except using a different view. 
						// 03/08/2011 Paul.  We need to make sure not to exceed 30 characters in the alias name. 
						if ( bEnableDynamicTeams )
							sbWhere.AppendLine("   and (" + sMODULE_NAME + ".TEAM_SET_ID is null or " + Sql.MetadataName(con, "vwTEAM_SET_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_TEAM_SET_ID is not null)");
						else
							sbWhere.AppendLine("   and (" + sMODULE_NAME + ".TEAM_ID is null or " + Sql.MetadataName(con, "vwTEAM_MEMBERSHIPS_" + sMODULE_NAME) + ".MEMBERSHIP_ID is not null)");
					}
				}
			}
			if ( bModuleIsAssigned )
			{
				int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, sACCESS_TYPE);
				// 11/27/2009 Paul.  Make sure owner rule does not apply to admins. 
				if ( nACLACCESS == ACL_ACCESS.OWNER || (bRequireUserAssignment && !bIsAdmin) )
				{
					sASSIGNED_USER_ID_Field = sMODULE_NAME + "." + sASSIGNED_USER_ID_Field;
					string sFieldPlaceholder = "MEMBERSHIP_USER_ID";  //Sql.NextPlaceholder(cmd, sASSIGNED_USER_ID_Field);
					// 01/22/2007 Paul.  If ASSIGNED_USER_ID is null, then let everybody see it. 
					// This was added to work around a bug whereby the ASSIGNED_USER_ID was not automatically assigned to the creating user. 
					bool bShowUnassigned = Crm.Config.show_unassigned();
					if ( bShowUnassigned )
					{
						if ( bIsCaseSignificantDB )
							sbWhere.AppendLine("   and (" + sASSIGNED_USER_ID_Field + " is null or upper(" + sASSIGNED_USER_ID_Field + ") = upper(@" + sFieldPlaceholder + "))");
						else
							sbWhere.AppendLine("   and (" + sASSIGNED_USER_ID_Field + " is null or "       + sASSIGNED_USER_ID_Field +  " = @"       + sFieldPlaceholder + ")" );
					}
					else
					{
						if ( bIsCaseSignificantDB )
							sbWhere.AppendLine("   and upper(" + sASSIGNED_USER_ID_Field + ") = upper(@" + sFieldPlaceholder + ")");
						else
							sbWhere.AppendLine("   and "       + sASSIGNED_USER_ID_Field +  " = @"       + sFieldPlaceholder      );
					}
					//Sql.AddParameter(cmd, "@" + sFieldPlaceholder, Security.USER_ID);
				}
			}
		}

		private void BuildReportSQL()
		{
			bool bIsOracle     = false;
			bool bIsDB2        = false;
			bool bIsMySQL      = false;
			bool bIsPostgreSQL = false;
			string sSplendidProvider = Sql.ToString(Application["SplendidProvider"]);
			DbProviderFactory dbf = DbProviderFactories.GetFactory();
			using ( IDbConnection con = dbf.CreateConnection() )
			{
				using ( IDbCommand cmd = con.CreateCommand() )
				{
					bIsOracle     = Sql.IsOracle    (cmd);
					bIsDB2        = Sql.IsDB2       (cmd);
					bIsMySQL      = Sql.IsMySQL     (cmd);
					bIsPostgreSQL = Sql.IsPostgreSQL(cmd);
				}
			}
			
			StringBuilder sb = new StringBuilder();
			StringBuilder sbACLWhere = new StringBuilder();
			if ( rdl.DocumentElement != null )
			{
				string sMODULE_TABLE = Sql.ToString(Application["Modules." + lstMODULE.SelectedValue + ".TableName"]);
				int nMaxLen = Math.Max(sMODULE_TABLE.Length, 15);
				Hashtable hashRequiredModules  = new Hashtable();
				Hashtable hashAvailableModules = new Hashtable();
				sb.Append("select ");
				
				bool bSelectAll = true;
				// 05/29/2006 Paul.  If the module is used in a filter, then it is required. 
				XmlDocument xmlDisplayColumns = rdl.GetCustomProperty("DisplayColumns");
				XmlNodeList nlFields = xmlDisplayColumns.DocumentElement.SelectNodes("DisplayColumn/Field");
				foreach ( XmlNode xField in nlFields )
					nMaxLen = Math.Max(nMaxLen, xField.InnerText.Length);
				
				// 01/10/2010 Paul.  The ProspectList Dynamic SQL must only return an ID. 
				if ( bPrimaryKeyOnly )
				{
					sb.AppendLine(sMODULE_TABLE + ".ID");
				}
				else
				{
					string sFieldSeparator = "";
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						foreach ( XmlNode xField in nlFields )
						{
							bSelectAll = false;
							sb.Append(sFieldSeparator);
							string sMODULE_ALIAS = xField.InnerText.Split('.')[0];
							if ( !hashRequiredModules.ContainsKey(sMODULE_ALIAS) )
								hashRequiredModules.Add(sMODULE_ALIAS, null);
							sb.Append(xField.InnerText);
							sb.Append(Strings.Space(nMaxLen - xField.InnerText.Length));
							// 03/08/2011 Paul.  We need to make sure not to exceed 30 characters in the alias name. 
							sb.Append(" as \"" + Sql.MetadataName(con, xField.InnerText) + "\"");
							sb.AppendLine();
							sFieldSeparator = "     , ";
						}
						if ( bSelectAll )
						{
							sb.AppendLine("*");
						}
					}
				}
				
				// 05/29/2006 Paul.  If the module is used in a filter, then it is required. 
				XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
				XmlNodeList nlFilters = xmlFilters.DocumentElement.SelectNodes("Filter");
				foreach ( XmlNode xFilter in nlFilters )
				{
					string sDATA_FIELD = XmlUtil.SelectSingleNode(xFilter, "DATA_FIELD");
					string sMODULE_ALIAS = sDATA_FIELD.Split('.')[0];
					if ( !hashRequiredModules.ContainsKey(sMODULE_ALIAS) )
						hashRequiredModules.Add(sMODULE_ALIAS, null);
				}

				if ( hashRequiredModules.ContainsKey(sMODULE_TABLE) )
					hashRequiredModules.Remove(sMODULE_TABLE);
				
				sb.AppendLine("  from            vw" + sMODULE_TABLE + " " + Strings.Space(nMaxLen - sMODULE_TABLE.Length) + sMODULE_TABLE);
				// 01/10/2010 Paul.  The Compaigns module will not need user-specific filtering. 
				if ( bUserSpecific )
				{
					// 04/17/2007 Paul.  Apply ACL rules. 
					if ( sMODULE_TABLE != "USERS" )
						ACLFilter(sb, sbACLWhere, sMODULE_TABLE, "list", "ASSIGNED_USER_ID", bIsOracle || bIsDB2);
				}
				hashAvailableModules.Add(sMODULE_TABLE, sMODULE_TABLE);
				if ( !Sql.IsEmptyString(lstRELATED.SelectedValue) )
				{
					XmlDocument xmlRelatedModules = rdl.GetCustomProperty("RelatedModules");
					string sRELATED       = lstRELATED.SelectedValue.Split(' ')[0];
					string sRELATED_ALIAS = lstRELATED.SelectedValue.Split(' ')[1];
					
					if ( hashRequiredModules.ContainsKey(sRELATED_ALIAS) )
						hashRequiredModules.Remove(sRELATED_ALIAS);

					XmlNode xRelationship = xmlRelatedModules.DocumentElement.SelectSingleNode("Relationship[RHS_MODULE=\'" + sRELATED + "\']");
					if ( xRelationship != null )
					{
						string sRELATIONSHIP_NAME              = XmlUtil.SelectSingleNode(xRelationship, "RELATIONSHIP_NAME"             );
						//string sLHS_MODULE                     = XmlUtil.SelectSingleNode(xRelationship, "LHS_MODULE"                    );
						string sLHS_TABLE                      = XmlUtil.SelectSingleNode(xRelationship, "LHS_TABLE"                     );
						string sLHS_KEY                        = XmlUtil.SelectSingleNode(xRelationship, "LHS_KEY"                       );
						//string sRHS_MODULE                     = XmlUtil.SelectSingleNode(xRelationship, "RHS_MODULE"                    );
						string sRHS_TABLE                      = XmlUtil.SelectSingleNode(xRelationship, "RHS_TABLE"                     );
						string sRHS_KEY                        = XmlUtil.SelectSingleNode(xRelationship, "RHS_KEY"                       );
						string sJOIN_TABLE                     = XmlUtil.SelectSingleNode(xRelationship, "JOIN_TABLE"                    );
						string sJOIN_KEY_LHS                   = XmlUtil.SelectSingleNode(xRelationship, "JOIN_KEY_LHS"                  );
						string sJOIN_KEY_RHS                   = XmlUtil.SelectSingleNode(xRelationship, "JOIN_KEY_RHS"                  );
						// 11/20/2008 Paul.  Quotes, Orders and Invoices have a relationship column. 
						string sRELATIONSHIP_ROLE_COLUMN       = XmlUtil.SelectSingleNode(xRelationship, "RELATIONSHIP_ROLE_COLUMN"      );
						string sRELATIONSHIP_ROLE_COLUMN_VALUE = XmlUtil.SelectSingleNode(xRelationship, "RELATIONSHIP_ROLE_COLUMN_VALUE");
						if ( Sql.IsEmptyString(sJOIN_TABLE) )
						{
							nMaxLen = Math.Max(nMaxLen, sRHS_TABLE.Length + sRHS_KEY.Length + 1);
							sb.AppendLine("       inner join vw" + sRHS_TABLE + " "            + Strings.Space(nMaxLen - sRHS_TABLE.Length                      ) + sRHS_TABLE);
							sb.AppendLine("               on "   + sRHS_TABLE + "." + sRHS_KEY + Strings.Space(nMaxLen - sRHS_TABLE.Length - sRHS_KEY.Length - 1) + " = " + sLHS_TABLE + "." + sLHS_KEY);
							// 05/05/2010 Paul.  The Compaigns module will not need user-specific filtering. 
							if ( bUserSpecific )
							{
								// 04/17/2007 Paul.  Apply ACL rules. 
								if ( sRHS_TABLE != "USERS" )
									ACLFilter(sb, sbACLWhere, sRHS_TABLE, "list", "ASSIGNED_USER_ID", bIsOracle || bIsDB2);
							}
						}
						else
						{
							nMaxLen = Math.Max(nMaxLen, sJOIN_TABLE.Length + sJOIN_KEY_LHS.Length + 1);
							nMaxLen = Math.Max(nMaxLen, sRHS_TABLE.Length + sRHS_KEY.Length      + 1);
							sb.AppendLine("       inner join vw" + sJOIN_TABLE + " "                 + Strings.Space(nMaxLen - sJOIN_TABLE.Length                           ) + sJOIN_TABLE);
							sb.AppendLine("               on "   + sJOIN_TABLE + "." + sJOIN_KEY_LHS + Strings.Space(nMaxLen - sJOIN_TABLE.Length - sJOIN_KEY_LHS.Length - 1) + " = " + sLHS_TABLE  + "." + sLHS_KEY     );
							// 10/31/2009 Paul.  The value should be escaped. 
							if ( !Sql.IsEmptyString(sRELATIONSHIP_ROLE_COLUMN) && !Sql.IsEmptyString(sRELATIONSHIP_ROLE_COLUMN_VALUE) )
								sb.AppendLine("              and "   + sJOIN_TABLE + "." + sRELATIONSHIP_ROLE_COLUMN + " = N'" + Sql.EscapeSQL(sRELATIONSHIP_ROLE_COLUMN_VALUE) + "'");
							sb.AppendLine("       inner join vw" + sRHS_TABLE + " "                  + Strings.Space(nMaxLen - sRHS_TABLE.Length                            ) + sRHS_TABLE);
							sb.AppendLine("               on "   + sRHS_TABLE + "." + sRHS_KEY       + Strings.Space(nMaxLen - sRHS_TABLE.Length - sRHS_KEY.Length - 1      ) + " = " + sJOIN_TABLE + "." + sJOIN_KEY_RHS);
							// 05/05/2010 Paul.  The Compaigns module will not need user-specific filtering. 
							if ( bUserSpecific )
							{
								// 04/17/2007 Paul.  Apply ACL rules. 
								if ( sRHS_TABLE != "USERS" )
									ACLFilter(sb, sbACLWhere, sRHS_TABLE, "list", "ASSIGNED_USER_ID", bIsOracle || bIsDB2);
							}
						}
						if ( !hashAvailableModules.ContainsKey(sRHS_TABLE) )
							hashAvailableModules.Add(sRHS_TABLE, sRHS_TABLE);
					}
				}
				if ( hashRequiredModules.Count > 0 )
				{
					XmlDocument xmlRelationships = rdl.GetCustomProperty("Relationships");
					foreach ( string sMODULE_ALIAS in hashRequiredModules.Keys )
					{
						XmlNode xRelationship = xmlRelationships.DocumentElement.SelectSingleNode("Relationship[MODULE_ALIAS=\'" + sMODULE_ALIAS + "\']");
						if ( xRelationship != null )
						{
							string sRELATIONSHIP_NAME = XmlUtil.SelectSingleNode(xRelationship, "RELATIONSHIP_NAME");
							//string sLHS_MODULE        = XmlUtil.SelectSingleNode(xRelationship, "LHS_MODULE"       );
							string sLHS_TABLE         = XmlUtil.SelectSingleNode(xRelationship, "LHS_TABLE"        );
							string sLHS_KEY           = XmlUtil.SelectSingleNode(xRelationship, "LHS_KEY"          );
							//string sRHS_MODULE        = XmlUtil.SelectSingleNode(xRelationship, "RHS_MODULE"       );
							string sRHS_TABLE         = XmlUtil.SelectSingleNode(xRelationship, "RHS_TABLE"        );
							string sRHS_KEY           = XmlUtil.SelectSingleNode(xRelationship, "RHS_KEY"          );
							nMaxLen = Math.Max(nMaxLen, sLHS_TABLE.Length );
							nMaxLen = Math.Max(nMaxLen, sMODULE_ALIAS.Length + sLHS_KEY.Length + 1);
							sb.AppendLine("  left outer join vw" + sLHS_TABLE + " "               + Strings.Space(nMaxLen - sLHS_TABLE.Length                        ) + sMODULE_ALIAS);
							sb.AppendLine("               on "   + sMODULE_ALIAS + "." + sLHS_KEY + Strings.Space(nMaxLen - sMODULE_ALIAS.Length - sLHS_KEY.Length - 1) + " = " + sRHS_TABLE + "." + sRHS_KEY);
							// 05/05/2010 Paul.  The Compaigns module will not need user-specific filtering. 
							if ( bUserSpecific )
							{
								// 04/17/2007 Paul.  Apply ACL rules. 
								if ( sLHS_TABLE != "USERS" )
									ACLFilter(sb, sbACLWhere, sMODULE_ALIAS, "list", "ASSIGNED_USER_ID", bIsOracle || bIsDB2);
							}
							// 07/13/2006 Paul.  The key needs to be the alias, and the value is the main table. 
							// This is because the same table may be referenced more than once, 
							// such as the Users table to display the last modified user and the assigned to user. 
							if ( !hashAvailableModules.ContainsKey(sMODULE_ALIAS) )
								hashAvailableModules.Add(sMODULE_ALIAS, sLHS_TABLE);
						}
					}
				}
				sb.AppendLine(" where 1 = 1");
				sb.Append(sbACLWhere.ToString());
				try
				{
					rdl.SetSingleNode("DataSets/DataSet/Query/QueryParameters", String.Empty);
					XmlNode xQueryParameters = rdl.SelectNode("DataSets/DataSet/Query/QueryParameters");
					xQueryParameters.RemoveAll();
					if ( xmlFilters.DocumentElement != null )
					{
						int nParameterIndex = 0;
						foreach ( XmlNode xFilter in xmlFilters.DocumentElement )
						{
							string sMODULE_NAME    = XmlUtil.SelectSingleNode(xFilter, "MODULE_NAME");
							string sDATA_FIELD     = XmlUtil.SelectSingleNode(xFilter, "DATA_FIELD" );
							string sDATA_TYPE      = XmlUtil.SelectSingleNode(xFilter, "DATA_TYPE"  );
							string sOPERATOR       = XmlUtil.SelectSingleNode(xFilter, "OPERATOR"   );
							// 07/04/2006 Paul.  We need to use the parameter index in the parameter name 
							// because a parameter can be used more than once and we need a unique name. 
							string sPARAMETER_NAME = RdlDocument.RdlParameterName(sDATA_FIELD, nParameterIndex, false);
							string sSECONDARY_NAME = RdlDocument.RdlParameterName(sDATA_FIELD, nParameterIndex, true );
							string sSEARCH_TEXT1   = String.Empty;
							string sSEARCH_TEXT2   = String.Empty;
							// 03/14/2011 Paul.  Oracle does not like parameter names longer than 30 characters. 
							if ( bIsOracle && (sPARAMETER_NAME.Length > 30 || sSECONDARY_NAME.Length > 30) )
							{
								sPARAMETER_NAME = "@PARAMETER__" + nParameterIndex.ToString("00") + "A";
								sSECONDARY_NAME = "@PARAMETER__" + nParameterIndex.ToString("00") + "B";
							}
							
							XmlNodeList nlValues = xFilter.SelectNodes("SEARCH_TEXT_VALUES");
							string[] arrSEARCH_TEXT = new string[nlValues.Count];
							int i = 0;
							foreach ( XmlNode xValue in nlValues )
							{
								arrSEARCH_TEXT[i++] = xValue.InnerText;
							}
							if ( arrSEARCH_TEXT.Length > 0 )
								sSEARCH_TEXT1 = arrSEARCH_TEXT[0];
							if ( arrSEARCH_TEXT.Length > 1 )
								sSEARCH_TEXT2 = arrSEARCH_TEXT[1];

							string sSQL = string.Empty;
							// 07/09/2007 Paul.  ansistring is treated the same as string. 
							string sCOMMON_DATA_TYPE = sDATA_TYPE;
							if ( sCOMMON_DATA_TYPE == "ansistring" )
								sCOMMON_DATA_TYPE = "string";
							switch ( sCOMMON_DATA_TYPE )
							{
								case "string":
								{
									// 07/16/2006 Paul.  Oracle and DB2 are case-significant.  Keep SQL Server code fast by not converting to uppercase. 
									if ( bIsOracle || bIsDB2 )
									{
										sSEARCH_TEXT1 = sSEARCH_TEXT1.ToUpper();
										sSEARCH_TEXT2 = sSEARCH_TEXT2.ToUpper();
										sDATA_FIELD   = "upper(" + sDATA_FIELD + ")";
									}
									switch ( sOPERATOR )
									{
										case "equals"        :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + "N'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "contains"      :
											sSQL = '%' + Sql.EscapeSQLLike(sSEARCH_TEXT1) + '%';
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "starts_with"   :
											sSQL =       Sql.EscapeSQLLike(sSEARCH_TEXT1) + '%';
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "ends_with"     :
											sSQL = '%' + Sql.EscapeSQLLike(sSEARCH_TEXT1)      ;
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "not_equals_str":  sb.AppendLine("   and " + sDATA_FIELD + " <> "   + "N'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "empty"         :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
								case "datetime":
								{
									string fnPrefix = "dbo.";
									if ( bIsOracle || bIsDB2 || bIsMySQL || bIsPostgreSQL )
									{
										fnPrefix = "";
									}
									if ( arrSEARCH_TEXT.Length > 0 )
									{
										//CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat
										DateTime dtSEARCH_TEXT1 = DateTime.MinValue;
										DateTime dtSEARCH_TEXT2 = DateTime.MinValue;
										int nINTERVAL = 0;
										// 11/16/2008 Paul.  Days old. 
										if ( !(sOPERATOR.EndsWith("_after") || sOPERATOR.EndsWith("_before") || sOPERATOR.EndsWith("_old")) )
										{
											dtSEARCH_TEXT1 = DateTime.ParseExact(sSEARCH_TEXT1, "yyyy/MM/dd", Thread.CurrentThread.CurrentCulture.DateTimeFormat);
											dtSEARCH_TEXT2 = DateTime.MinValue;
											if ( arrSEARCH_TEXT.Length > 1 )
											{
												dtSEARCH_TEXT2 = DateTime.ParseExact(sSEARCH_TEXT2, "yyyy/MM/dd", Thread.CurrentThread.CurrentCulture.DateTimeFormat);
												if ( bIsOracle )
													sSEARCH_TEXT2 = "to_date('" + dtSEARCH_TEXT2.ToString("yyyy-MM-dd") + "','YYYY-MM-DD')";
												else
													sSEARCH_TEXT2 = "'" + dtSEARCH_TEXT2.ToString("yyyy/MM/dd") + "'";
											}
											if ( bIsOracle )
												sSEARCH_TEXT1 = "to_date('" + dtSEARCH_TEXT1.ToString("yyyy-MM-dd") + "','YYYY-MM-DD')";
											else
												sSEARCH_TEXT1 = "'" + dtSEARCH_TEXT1.ToString("yyyy/MM/dd") + "'";
										}
										else
										{
											nINTERVAL = Sql.ToInteger(sSEARCH_TEXT1);
										}
										switch ( sOPERATOR )
										{
											case "on"               :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") = "  + sSEARCH_TEXT1);  break;
											case "before"           :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") < "  + sSEARCH_TEXT1);  break;
											case "after"            :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") > "  + sSEARCH_TEXT1);  break;
											case "not_equals_str"   :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") <> " + sSEARCH_TEXT1);  break;
											case "between_dates"    :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") between " + sSEARCH_TEXT1 + " and " + sSEARCH_TEXT2);  break;
											// 11/16/2008 Paul.  Days old. 
											case "tp_days_after"    :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " > "       + fnPrefix + "fnDateAdd('day', "    +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_weeks_after"   :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " > "       + fnPrefix + "fnDateAdd('week', "   +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_months_after"  :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " > "       + fnPrefix + "fnDateAdd('month', "  +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_years_after"   :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " > "       + fnPrefix + "fnDateAdd('year', "   +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_days_before"   :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " between " + fnPrefix + "fnDateAdd('day', "    + (-nINTERVAL)  .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")) and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")");  break;
											case "tp_weeks_before"  :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " between " + fnPrefix + "fnDateAdd('week', "   + (-nINTERVAL)  .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")) and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")");  break;
											case "tp_months_before" :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " between " + fnPrefix + "fnDateAdd('month', "  + (-nINTERVAL)  .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")) and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")");  break;
											case "tp_years_before"  :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " between " + fnPrefix + "fnDateAdd('year', "   + (-nINTERVAL)  .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")) and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ")");  break;
											case "tp_minutes_after" :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "GETDATE()") + " between " + fnPrefix + "fnDateAdd('minute', " +   nINTERVAL   .ToString() + ", " + sDATA_FIELD                             + ") and " + fnPrefix + "fnDateAdd('minute', " + (1+nINTERVAL).ToString() + ", " + sDATA_FIELD + ")");  break;
											case "tp_hours_after"   :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "GETDATE()") + " between " + fnPrefix + "fnDateAdd('hour', "   +   nINTERVAL   .ToString() + ", " + sDATA_FIELD                             + ") and " + fnPrefix + "fnDateAdd('hour', "   + (1+nINTERVAL).ToString() + ", " + sDATA_FIELD + ")");  break;
											case "tp_minutes_before":  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "GETDATE()") + " between " + fnPrefix + "fnDateAdd('minute', " + (-nINTERVAL-1).ToString() + ", " + sDATA_FIELD                             + ") and " + fnPrefix + "fnDateAdd('minute', " +  (-nINTERVAL).ToString() + ", " + sDATA_FIELD + ")");  break;
											case "tp_hours_before"  :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "GETDATE()") + " between " + fnPrefix + "fnDateAdd('hour', "   + (-nINTERVAL-1).ToString() + ", " + sDATA_FIELD                             + ") and " + fnPrefix + "fnDateAdd('hour', "   +  (-nINTERVAL).ToString() + ", " + sDATA_FIELD + ")");  break;
											// 12/04/2008 Paul.  We need to be able to do an an equals. 
											case "tp_days_old"      :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " = "       + fnPrefix + "fnDateAdd('day', "    +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_weeks_old"     :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " = "       + fnPrefix + "fnDateAdd('week', "   +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_months_old"    :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " = "       + fnPrefix + "fnDateAdd('month', "  +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
											case "tp_years_old"     :  sb.AppendLine("   and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"  ) + " = "       + fnPrefix + "fnDateAdd('year', "   +   nINTERVAL   .ToString() + ", " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + "))");  break;
										}
									}
									else
									{
										switch ( sOPERATOR )
										{
											case "empty"            :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
											case "not_empty"        :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
											case "is_before"        :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") < " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"                  ));  break;
											case "is_after"         :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") > " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"                  ));  break;
											case "tp_yesterday"     :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, -1, TODAY())"));  break;
											case "tp_today"         :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"                  ));  break;
											case "tp_tomorrow"      :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, 1, TODAY())" ));  break;
											case "tp_last_7_days"   :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") between " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, -7, TODAY())") + " and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"));
												break;
											case "tp_next_7_days"   :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") between " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()" ) + " and " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, 7, TODAY())"));
												break;
											// 07/05/2006 Paul.  Month math must also include the year. 
											case "tp_last_month"    :  sb.AppendLine("   and month(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "MONTH(DATEADD(MONTH, -1, TODAY()))"));
											                           sb.AppendLine("   and year("  + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(DATEADD(MONTH, -1, TODAY()))" ));
												break;
											case "tp_this_month"    :  sb.AppendLine("   and month(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "MONTH(TODAY())"));
											                           sb.AppendLine("   and year("  + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(TODAY())" ));
												break;
											case "tp_next_month"    :  sb.AppendLine("   and month(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "MONTH(DATEADD(MONTH, 1, TODAY()))"));
											                           sb.AppendLine("   and year("  + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(DATEADD(MONTH, 1, TODAY()))" ));
												break;
											case "tp_last_30_days"  :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") between " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, -30, TODAY())") + " and " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()"));
												break;
											case "tp_next_30_days"  :  sb.AppendLine("   and " + fnPrefix + "fnDateOnly(" + sDATA_FIELD + ") between " + RdlDocument.DbSpecificDate(sSplendidProvider, "TODAY()") + " and " + RdlDocument.DbSpecificDate(sSplendidProvider, "DATEADD(DAY, 30, TODAY())"));
												break;
											case "tp_last_year"     :  sb.AppendLine("   and year(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(DATEADD(YEAR, -1, TODAY()))"));  break;
											case "tp_this_year"     :  sb.AppendLine("   and year(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(TODAY())"                   ));  break;
											case "tp_next_year"     :  sb.AppendLine("   and year(" + sDATA_FIELD + ") = " + RdlDocument.DbSpecificDate(sSplendidProvider, "YEAR(DATEADD(YEAR, 1, TODAY()))" ));  break;
										}
									}
									break;
								}
								case "int32":
								{
									sSEARCH_TEXT1 = Sql.ToInteger(sSEARCH_TEXT1).ToString();
									sSEARCH_TEXT2 = Sql.ToInteger(sSEARCH_TEXT2).ToString();
									switch ( sOPERATOR )
									{
										case "equals"    :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + sSEARCH_TEXT1);  break;
										case "less"      :  sb.AppendLine("   and " + sDATA_FIELD + " < "    + sSEARCH_TEXT1);  break;
										case "greater"   :  sb.AppendLine("   and " + sDATA_FIELD + " > "    + sSEARCH_TEXT1);  break;
										case "not_equals":  sb.AppendLine("   and " + sDATA_FIELD + " <> "   + sSEARCH_TEXT1);  break;
										case "between"   :  sb.AppendLine("   and " + sDATA_FIELD + " between "   + sSEARCH_TEXT1 + " and " + sSEARCH_TEXT2);  break;
										case "empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty" :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
								case "decimal":
								{
									sSEARCH_TEXT1 = Sql.ToDecimal(sSEARCH_TEXT1).ToString();
									sSEARCH_TEXT2 = Sql.ToDecimal(sSEARCH_TEXT2).ToString();
									switch ( sOPERATOR )
									{
										case "equals"    :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + sSEARCH_TEXT1);  break;
										case "less"      :  sb.AppendLine("   and " + sDATA_FIELD + " < "    + sSEARCH_TEXT1);  break;
										case "greater"   :  sb.AppendLine("   and " + sDATA_FIELD + " > "    + sSEARCH_TEXT1);  break;
										case "not_equals":  sb.AppendLine("   and " + sDATA_FIELD + " <> "   + sSEARCH_TEXT1);  break;
										case "between"   :  sb.AppendLine("   and " + sDATA_FIELD + " between "   + sSEARCH_TEXT1 + " and " + sSEARCH_TEXT2);  break;
										case "empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty" :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
								case "float":
								{
									sSEARCH_TEXT1 = Sql.ToFloat(sSEARCH_TEXT1).ToString();
									sSEARCH_TEXT2 = Sql.ToFloat(sSEARCH_TEXT2).ToString();
									switch ( sOPERATOR )
									{
										case "equals"    :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + sSEARCH_TEXT1);  break;
										case "less"      :  sb.AppendLine("   and " + sDATA_FIELD + " < "    + sSEARCH_TEXT1);  break;
										case "greater"   :  sb.AppendLine("   and " + sDATA_FIELD + " > "    + sSEARCH_TEXT1);  break;
										case "not_equals":  sb.AppendLine("   and " + sDATA_FIELD + " <> "   + sSEARCH_TEXT1);  break;
										case "between"   :  sb.AppendLine("   and " + sDATA_FIELD + " between "   + sSEARCH_TEXT1 + " and " + sSEARCH_TEXT2);  break;
										case "empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty" :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
								case "bool":
								{
									sSEARCH_TEXT1 = Sql.ToBoolean(sSEARCH_TEXT1) ? "1" : "0";
									switch ( sOPERATOR )
									{
										case "equals"    :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + sSEARCH_TEXT1);  break;
										case "empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty" :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
								case "guid":
								{
									// 07/16/2006 Paul.  Oracle and DB2 are case-significant.  Keep SQL Server code fast by not converting to uppercase. 
									if ( bIsOracle || bIsDB2 )
									{
										sSEARCH_TEXT1 = sSEARCH_TEXT1.ToUpper();
										sSEARCH_TEXT2 = sSEARCH_TEXT2.ToUpper();
										sDATA_FIELD   = "upper(" + sDATA_FIELD + ")";
									}
									switch ( sOPERATOR )
									{
										case "is"            :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + "'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "equals"        :  sb.AppendLine("   and " + sDATA_FIELD + " = "    + "'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "contains"      :
											sSQL = '%' + Sql.EscapeSQLLike(sSEARCH_TEXT1) + '%';
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "starts_with"   :
											sSQL =       Sql.EscapeSQLLike(sSEARCH_TEXT1) + '%';
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "ends_with"     :
											sSQL = '%' + Sql.EscapeSQLLike(sSEARCH_TEXT1)      ;
											// 01/10/2010 Paul.  PostgreSQL requires two slashes. 
											if ( bIsMySQL || bIsPostgreSQL )
												sSQL = sSQL.Replace("\\", "\\\\");  // 07/16/2006 Paul.  MySQL requires that slashes be escaped, even in the escape clause. 
											sb.AppendLine("   and " + sDATA_FIELD + " like " + "N'" + Sql.EscapeSQL(sSQL) + "'" + (bIsMySQL ? " escape '\\\\'" : " escape '\\'"));
											break;
										case "not_equals_str":  sb.AppendLine("   and " + sDATA_FIELD + " <> "   + "'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "empty"         :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
										// 05/05/2010 Paul.  one_of was available in the UI, but was not generating the SQL. 
										case "one_of":
										{
											// 12/03/2008 Paul.  arrSEARCH_TEXT should already be populated.  Do not pull from lstFILTER_SEARCH_LISTBOX. 
											if ( arrSEARCH_TEXT != null && arrSEARCH_TEXT.Length > 0 )
											{
												sb.Append("   and " + sDATA_FIELD + " in (");
												for ( int j = 0; j < arrSEARCH_TEXT.Length; j++ )
												{
													if ( j > 0 )
														sb.Append(", ");
													sb.Append("N'" + Sql.EscapeSQL(arrSEARCH_TEXT[j]) + "'");
												}
												sb.AppendLine(")");
											}
											break;
										}
									}
									break;
								}
								case "enum":
								{
									// 07/16/2006 Paul.  Oracle and DB2 are case-significant.  Keep SQL Server code fast by not converting to uppercase. 
									if ( bIsOracle || bIsDB2 )
									{
										sSEARCH_TEXT1 = sSEARCH_TEXT1.ToUpper();
										sSEARCH_TEXT2 = sSEARCH_TEXT2.ToUpper();
										sDATA_FIELD   = "upper(" + sDATA_FIELD + ")";
									}
									switch ( sOPERATOR )
									{
										// 02/09/2007 Paul.  enum uses is instead of equals operator. 
										case "is"    :  sb.AppendLine("   and " + sDATA_FIELD + " = "   + "N'" + Sql.EscapeSQL(sSEARCH_TEXT1) + "'");  break;
										case "one_of":
										{
											// 12/03/2008 Paul.  arrSEARCH_TEXT should already be populated.  Do not pull from lstFILTER_SEARCH_LISTBOX. 
											if ( arrSEARCH_TEXT != null && arrSEARCH_TEXT.Length > 0 )
											{
												sb.Append("   and " + sDATA_FIELD + " in (");
												for ( int j = 0; j < arrSEARCH_TEXT.Length; j++ )
												{
													if ( j > 0 )
														sb.Append(", ");
													sb.Append("N'" + Sql.EscapeSQL(arrSEARCH_TEXT[j]) + "'");
												}
												sb.AppendLine(")");
											}
											break;
										}
										case "empty"         :  sb.AppendLine("   and " + sDATA_FIELD + " is null"    );  break;
										case "not_empty"     :  sb.AppendLine("   and " + sDATA_FIELD + " is not null");  break;
									}
									break;
								}
							}
							nParameterIndex++;
						}
					}
					// 06/18/2006 Paul.  The element 'QueryParameters' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:QueryParameter'. 
					if ( xQueryParameters.ChildNodes.Count == 0 )
					{
						xQueryParameters.ParentNode.RemoveChild(xQueryParameters);
					}
				}
				catch(Exception ex)
				{
					lblError.Text = ex.Message;
				}
				// 06/15/2006 Paul.  Completely rebuild the Fields list based on the available modules. 
				rdl.SetSingleNode("DataSets/DataSet/Fields", String.Empty);
				XmlNode xFields = rdl.SelectNode("DataSets/DataSet/Fields");
				xFields.RemoveAll();
				// 07/13/2006 Paul.  The key is the alias and the value is the module. 
				// This is so that the same module can be referenced many times with many aliases. 
				foreach ( string sTableAlias in hashAvailableModules.Keys )
				{
					// 07/22/2008 Paul.  Not really a bug fix, but just a better field name.  The hash table contains table names and not module names. 
					string sTABLE_NAME = Sql.ToString(hashAvailableModules[sTableAlias]);
					DataTable dtColumns = SplendidCache.ReportingFilterColumns(sTABLE_NAME).Copy();
					foreach(DataRow row in dtColumns.Rows)
					{
						string sFieldName = sTableAlias + "." + Sql.ToString(row["NAME"]);
						string sCsType = Sql.ToString(row["CsType"]);
						string sFieldType = String.Empty;
						switch ( sCsType )
						{
							case "Guid"      :  sFieldType = "System.Guid"    ;  break;
							case "string"    :  sFieldType = "System.String"  ;  break;
							case "ansistring":  sFieldType = "System.String"  ;  break;
							case "DateTime"  :  sFieldType = "System.DateTime";  break;
							case "bool"      :  sFieldType = "System.Boolean" ;  break;
							case "float"     :  sFieldType = "System.Double"  ;  break;
							case "decimal"   :  sFieldType = "System.Decimal" ;  break;
							case "short"     :  sFieldType = "System.Int16"   ;  break;
							case "Int32"     :  sFieldType = "System.Int32"   ;  break;
							case "Int64"     :  sFieldType = "System.Int64"   ;  break;
							default          :  sFieldType = "System.String"  ;  break;
						}
						rdl.CreateField(xFields, sFieldName, sFieldType);
					}
				}
			}
			sReportSQL = sb.ToString();
			rdl.SetSingleNode("DataSets/DataSet/Query/CommandText", sReportSQL);
		}

		private DataTable ReportFilters()
		{
			DataTable dtFilters = new DataTable();
			XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
			dtFilters = XmlUtil.CreateDataTable(xmlFilters.DocumentElement, "Filter", new string[] {"ID", "MODULE_NAME", "DATA_FIELD", "DATA_TYPE", "OPERATOR", "SEARCH_TEXT"});
			return dtFilters;
		}

		private DataTable ReportColumnSource()
		{
			DataTable dtColumnSource = new DataTable();
			XmlDocument xmlRelationships = rdl.GetCustomProperty("Relationships");
			dtColumnSource = XmlUtil.CreateDataTable(xmlRelationships.DocumentElement, "Relationship", new string[] {"MODULE_NAME", "DISPLAY_NAME"});
			return dtColumnSource;
		}

		#region Filter Editing
		protected void FiltersGet(string sID, ref string sMODULE_NAME, ref string sDATA_FIELD, ref string sDATA_TYPE, ref string sOPERATOR, ref string[] arrSEARCH_TEXT)
		{
			XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
			XmlNode xFilter = xmlFilters.DocumentElement.SelectSingleNode("Filter[ID=\'" + sID + "\']");
			if ( xFilter != null )
			{
				sMODULE_NAME = XmlUtil.SelectSingleNode(xFilter, "MODULE_NAME");
				sDATA_FIELD  = XmlUtil.SelectSingleNode(xFilter, "DATA_FIELD" );
				sDATA_TYPE   = XmlUtil.SelectSingleNode(xFilter, "DATA_TYPE"  );
				sOPERATOR    = XmlUtil.SelectSingleNode(xFilter, "OPERATOR"   );
				//sSEARCH_TEXT = XmlUtil.GetSingleNode(xFilter, "SEARCH_TEXT");
				XmlNodeList nlValues = xFilter.SelectNodes("SEARCH_TEXT_VALUES");
				arrSEARCH_TEXT = new string[nlValues.Count];
				int i = 0;
				foreach ( XmlNode xValue in nlValues )
				{
					arrSEARCH_TEXT[i++] = xValue.InnerText;
				}
			}
		}

		protected void RemoveInvalidDisplayColumns()
		{
			Hashtable hashMODULES = new Hashtable();
			string sMODULE_TABLE = Sql.ToString(Application["Modules." + lstMODULE.SelectedValue + ".TableName"]);
			hashMODULES.Add(sMODULE_TABLE, lstMODULE.SelectedValue);

			XmlDocument xmlRelationships = rdl.GetCustomProperty("Relationships");
			DataView vwModuleColumnSource = new DataView(XmlUtil.CreateDataTable(xmlRelationships.DocumentElement, "Relationship", new string[] { "MODULE_NAME", "MODULE_ALIAS", "DISPLAY_NAME", "RELATIONSHIP_TYPE" }));
			vwModuleColumnSource.RowFilter = "RELATIONSHIP_TYPE = 'one-to-many'";
			foreach ( DataRowView row in vwModuleColumnSource )
			{
				// 10/31/2008 Paul.  Make sure not to add the same module twice. 
				if ( !hashMODULES.ContainsKey(Sql.ToString(row["MODULE_ALIAS"])) )
					hashMODULES.Add(Sql.ToString(row["MODULE_ALIAS"]), Sql.ToString(row["MODULE_NAME"]));
			}
			// 07/13/2006 Paul.  Related may not exist, so not forget to check. 
			if ( lstRELATED.SelectedValue.IndexOf(' ') >= 0 )
			{
				string sRELATED       = lstRELATED.SelectedValue.Split(' ')[0];
				string sRELATED_ALIAS = lstRELATED.SelectedValue.Split(' ')[1];
				// 10/31/2008 Paul.  Make sure not to add the same module twice. 
				if ( !hashMODULES.ContainsKey(sRELATED_ALIAS) )
					hashMODULES.Add(sRELATED_ALIAS, sRELATED);
			}
			XmlDocument xmlDisplayColumns = rdl.GetCustomProperty("DisplayColumns");
			try
			{
				ArrayList arrDeleted = new ArrayList();
				XmlNodeList nlFields = xmlDisplayColumns.DocumentElement.SelectNodes("DisplayColumn/Field");
				foreach ( XmlNode xField in nlFields )
				{
					// 07/13/2006 Paul.  The column stores the module and the alias.  We need to verify the alias. 
					string sDATA_FIELD = xField.InnerText;
					if ( sDATA_FIELD.IndexOf('.') >= 0 )
					{
						string sMODULE_ALIAS = sDATA_FIELD.Split('.')[0];
						if ( !hashMODULES.ContainsKey(sMODULE_ALIAS) )
						{
							arrDeleted.Add(xField);
						}
					}
					else
					{
						// Delete filter if not formatted properly.  It must include the table alias. 
						arrDeleted.Add(xField);
					}
				}
				foreach ( XmlNode xField in arrDeleted )
				{
					rdl.RemoveField(xField.InnerText);
					xmlDisplayColumns.DocumentElement.RemoveChild(xField.ParentNode);
				}
				rdl.SetCustomProperty("DisplayColumns", xmlDisplayColumns.OuterXml.Replace("</DisplayColumn>", "</DisplayColumn>" + ControlChars.CrLf));
			}
			catch(Exception ex)
			{
				lblError.Text = ex.Message;
			}
		}

		protected void RemoveInvalidFilters()
		{
			Hashtable hashMODULES = new Hashtable();
			string sMODULE_TABLE = Sql.ToString(Application["Modules." + lstMODULE.SelectedValue + ".TableName"]);
			hashMODULES.Add(sMODULE_TABLE, lstMODULE.SelectedValue);

			XmlDocument xmlRelationships = rdl.GetCustomProperty("Relationships");
			DataView vwModuleColumnSource = new DataView(XmlUtil.CreateDataTable(xmlRelationships.DocumentElement, "Relationship", new string[] { "MODULE_NAME", "MODULE_ALIAS", "DISPLAY_NAME", "RELATIONSHIP_TYPE" }));
			vwModuleColumnSource.RowFilter = "RELATIONSHIP_TYPE = 'one-to-many'";
			foreach ( DataRowView row in vwModuleColumnSource )
			{
				// 10/31/2008 Paul.  Make sure not to add the same module twice. 
				if ( !hashMODULES.ContainsKey(Sql.ToString(row["MODULE_ALIAS"])) )
					hashMODULES.Add(Sql.ToString(row["MODULE_ALIAS"]), Sql.ToString(row["MODULE_NAME"]));
			}
			// 07/13/2006 Paul.  Related may not exist, so not forget to check. 
			if ( lstRELATED.SelectedValue.IndexOf(' ') >= 0 )
			{
				string sRELATED       = lstRELATED.SelectedValue.Split(' ')[0];
				string sRELATED_ALIAS = lstRELATED.SelectedValue.Split(' ')[1];
				// 10/31/2008 Paul.  Make sure not to add the same module twice. 
				if ( !hashMODULES.ContainsKey(sRELATED_ALIAS) )
					hashMODULES.Add(sRELATED_ALIAS, sRELATED);
			}

			XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
			try
			{
				ArrayList arrDeleted = new ArrayList();
				XmlNodeList nlFilters = xmlFilters.DocumentElement.SelectNodes("Filter");
				foreach ( XmlNode xFilter in nlFilters )
				{
					// 07/13/2006 Paul.  The filter stores the module and the alias.  We need to verify the alias. 
					string sDATA_FIELD = XmlUtil.SelectSingleNode(xFilter, "DATA_FIELD");
					if ( sDATA_FIELD.IndexOf('.') >= 0 )
					{
						string sMODULE_ALIAS = sDATA_FIELD.Split('.')[0];
						if ( !hashMODULES.ContainsKey(sMODULE_ALIAS) )
						{
							arrDeleted.Add(xFilter);
						}
					}
					else
					{
						// Delete filter if not formatted properly.  It must include the table alias. 
						arrDeleted.Add(xFilter);
					}
				}
				foreach ( XmlNode xFilter in arrDeleted )
				{
					xmlFilters.DocumentElement.RemoveChild(xFilter);
				}
				rdl.SetCustomProperty("Filters", xmlFilters.OuterXml);
				
				dgFilters.DataSource = ReportFilters();
				dgFilters.DataBind();
			}
			catch(Exception ex)
			{
				lblError.Text = ex.Message;
			}
		}

		protected void FiltersUpdate(string sID, string sMODULE_NAME, string sDATA_FIELD, string sDATA_TYPE, string sOPERATOR, string[] arrSEARCH_TEXT)
		{
			XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
			try
			{
				XmlNode xFilter = xmlFilters.DocumentElement.SelectSingleNode("Filter[ID=\'" + sID + "\']");
				if ( xFilter == null || Sql.IsEmptyString(sID) )
				{
					xFilter = xmlFilters.CreateElement("Filter");
					xmlFilters.DocumentElement.AppendChild(xFilter);
					XmlUtil.SetSingleNode(xmlFilters, xFilter, "ID", Guid.NewGuid().ToString());
				}
				else
				{
					// 06/12/2006 Paul.  The easiest way to remove the old text values is to delete them all. 
					xFilter.RemoveAll();
					XmlUtil.SetSingleNode(xmlFilters, xFilter, "ID", sID);
				}
				XmlUtil.SetSingleNode(xmlFilters, xFilter, "MODULE_NAME", sMODULE_NAME  );
				XmlUtil.SetSingleNode(xmlFilters, xFilter, "DATA_FIELD" , sDATA_FIELD   );
				XmlUtil.SetSingleNode(xmlFilters, xFilter, "DATA_TYPE"  , sDATA_TYPE    );
				XmlUtil.SetSingleNode(xmlFilters, xFilter, "OPERATOR"   , sOPERATOR     );
				XmlUtil.SetSingleNode(xmlFilters, xFilter, "SEARCH_TEXT", String.Join(", ", arrSEARCH_TEXT));
				foreach ( string sSEARCH_TEXT in arrSEARCH_TEXT )
				{
					XmlNode xSearchText = xmlFilters.CreateElement("SEARCH_TEXT_VALUES");
					xFilter.AppendChild(xSearchText);
					xSearchText.InnerText = sSEARCH_TEXT;
				}
				
				rdl.SetCustomProperty("Filters", xmlFilters.OuterXml);
				
				dgFilters.DataSource = ReportFilters();
				dgFilters.DataBind();
			}
			catch(Exception ex)
			{
				lblError.Text = ex.Message;
			}
		}

		protected void FiltersDelete(string sID)
		{
			dgFilters.EditItemIndex = -1;
			XmlDocument xmlFilters = rdl.GetCustomProperty("Filters");
			XmlNode xFilter = xmlFilters.DocumentElement.SelectSingleNode("Filter[ID=\'" + sID + "\']");
			if ( xFilter != null )
			{
				xFilter.ParentNode.RemoveChild(xFilter);
				rdl.SetCustomProperty("Filters", xmlFilters.OuterXml);
			}
			dgFilters.DataSource = ReportFilters();
			dgFilters.DataBind();
		}
		#endregion

		public void LoadRdl(string sRDL)
		{
			try
			{
				rdl = new RdlDocument();
				DataView vwModules = new DataView(SplendidCache.ReportingModules());
				if ( arrModules != null && arrModules.Length > 0 )
				{
					vwModules.RowFilter = "MODULE_NAME in ('" + String.Join("', '", arrModules) + "')";
				}
				vwModules.Sort = "DISPLAY_NAME";
				lstMODULE.DataSource = vwModules;
				lstMODULE.DataBind();
				lblMODULE.Text = lstMODULE.SelectedValue;
				
				try
				{
					if ( !Sql.IsEmptyString(sRDL) )
					{
						rdl.LoadRdl(sRDL);
						
						// 08/19/2010 Paul.  Check the list before assigning the value. 
						Utils.SetSelectedValue(lstMODULE, rdl.GetCustomPropertyValue("Module"));
						lblMODULE.Text = lstMODULE.SelectedValue;
					}
				}
				catch
				{
				}
				// 05/27/2006 Paul.  This is a catch-all statement to create a new report if all else fails. 
				if ( rdl.DocumentElement == null )
				{
					rdl = new RdlDocument(String.Empty, String.Empty);
					rdl.SetCustomProperty("Module"        , lstMODULE.SelectedValue  );
					rdl.SetCustomProperty("Related"       , lstRELATED.SelectedValue );
				}
				// 10/24/2010 Paul.  We need to clear the related control before rebinding, otherwise it will throw an exception if the selected value is already set. 
				// This was first encountered when used inthe RulesWizard. 
				lstRELATED.DataSource = null;
				lstRELATED.DataBind();
				lstRELATED_Bind();
				lblRELATED.Text = lstRELATED.SelectedValue;
				try
				{
					// 08/19/2010 Paul.  Check the list before assigning the value. 
					Utils.SetSelectedValue(lstRELATED, rdl.GetCustomPropertyValue("Related"));
				}
				catch
				{
				}
				// 07/26/2007 Paul.  The column sources need to be updated after the related has changed. 
				lstFILTER_COLUMN_SOURCE_Bind();
				BuildReportSQL();

				dgFilters.DataSource = ReportFilters();
				dgFilters.DataBind();
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				lblError.Text = ex.Message;
			}
		}

		private void Page_Load(object sender, System.EventArgs e)
		{
			lblMODULE              .Visible = bDebug;
			lblRELATED             .Visible = bDebug;
			lblMODULE_COLUMN_SOURCE.Visible = bDebug;
			lblFILTER_COLUMN_SOURCE.Visible = bDebug;
			lblFILTER_COLUMN       .Visible = bDebug;
			lblFILTER_OPERATOR_TYPE.Visible = bDebug;
			lblFILTER_OPERATOR     .Visible = bDebug;
			lblFILTER_ID           .Visible = bDebug;
			try
			{
				// 02/02/2010 Paul.  On initial load, the Dynamic List flag is disabled and the RDL does not get generated and saved in the PreRender 
				// because the control is not visible (and therefore not rendered).  So, if there is no RDL, then we need go generate it. 
				string sRdl = Sql.ToString(ViewState["rdl"]);
				if ( !IsPostBack || Sql.IsEmptyString(sRdl) )
				{
					// 07/13/2006 Paul.  We don't store the SHOW_QUERY value in the RDL, so we must retrieve it from the session. 
					chkSHOW_QUERY.Checked = Sql.ToBoolean(Session["ProspectLists.SHOW_QUERY"]);
					
					if ( rdl == null )
					{
						DataView vwModules = new DataView(SplendidCache.ReportingModules());
						if ( arrModules != null && arrModules.Length > 0 )
						{
							vwModules.RowFilter = "MODULE_NAME in ('" + String.Join("', '", arrModules) + "')";
						}
						vwModules.Sort = "DISPLAY_NAME";
						lstMODULE.DataSource = vwModules;
						lstMODULE.DataBind();
						lblMODULE.Text = lstMODULE.SelectedValue;
						
						rdl = new RdlDocument(String.Empty, String.Empty);
						rdl.SetCustomProperty("Module"        , lstMODULE.SelectedValue  );
						rdl.SetCustomProperty("Related"       , lstRELATED.SelectedValue );
						
						lstRELATED_Bind();
						lblRELATED.Text = lstRELATED.SelectedValue;
						try
						{
							// 08/19/2010 Paul.  Check the list before assigning the value. 
							Utils.SetSelectedValue(lstRELATED, rdl.GetCustomPropertyValue("Related"));
						}
						catch
						{
						}
						// 07/26/2007 Paul.  The column sources need to be updated after the related has changed. 
						lstFILTER_COLUMN_SOURCE_Bind();
						BuildReportSQL();

						dgFilters.DataSource = ReportFilters();
						dgFilters.DataBind();
					}
				}
				else
				{
					// 07/13/2006 Paul.  Save the SHOW_QUERY flag in the Session so that it will be available across redirects. 
					Session["ProspectLists.SHOW_QUERY"] = chkSHOW_QUERY.Checked;

					rdl = new RdlDocument();
					rdl.LoadRdl(sRdl);

					XmlDocument xmlDisplayColumns = rdl.GetCustomProperty("DisplayColumns");
					DataTable dtDisplayColumns = XmlUtil.CreateDataTable(xmlDisplayColumns.DocumentElement, "DisplayColumn", new string[] { "Label", "Field"});
					rdl.UpdateDataTable(dtDisplayColumns);
					xmlDisplayColumns = null;

					BuildReportSQL();

					dgFilters.DataSource = ReportFilters();
					dgFilters.DataBind();
				}
#if DEBUG
				//RegisterClientScriptBlock("ReportSQL", "<script type=\"text/javascript\">sDebugSQL += '" + Sql.EscapeJavaScript("\r" + sReportSQL) + "';</script>");
#endif
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				lblError.Text = ex.Message;
			}
		}

		private void Page_PreRender(object sender, System.EventArgs e)
		{
			if ( chkSHOW_QUERY.Checked )
			{
				litREPORT_QUERY.Text = "<br /><table border=\"1\" cellpadding=\"3\" cellspacing=\"0\" width=\"100%\" bgcolor=\"LightGrey\"><tr><td>";
				litREPORT_QUERY.Text += "<pre><b>" + sReportSQL + "</b></pre>";
				litREPORT_QUERY.Text += "</td></tr></table><br />";
#if DEBUG
				// 07/15/2010 Paul.  Use new function to format Rdl. 
				if ( rdl != null && rdl.DocumentElement != null)
					litREPORT_RDL.Text = RdlUtil.RdlEncode(rdl);
#endif
			}
			else
			{
				// 07/15/2010 Paul.  If not checked, we must clear the literal. 
				litREPORT_QUERY.Text = String.Empty;
			}
			ViewState["rdl"] = rdl.OuterXml;
		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		///		Required method for Designer support - do not modify
		///		the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.Load += new System.EventHandler(this.Page_Load);
			this.PreRender += new System.EventHandler(this.Page_PreRender);
		}
		#endregion
	}
}

